I need help on table design 2

[SCantoria]

I need the design to allow me to view the data as described below.

Hospital A
|_______> Entity A
| | |
| | V
| | Clinic A
| | - Jane Doe MD
| | - Janette Brown MD
| | ACME Clinic
| | - Jennifer Smith MD
| | Clinic of Jennifer Smith MD
| | - Jennifer Smith MD
| | - Billy Johnson MD
| |
| V
| - Earl Brown MD (Hospital Entity Resident Practitioner)
| - Janette Brown MD
V
- John Smith MD
- Bob Jones MD

Thank you,

Steve C.

 

[maxhugen]

You could use a 'node' model (such as in Drupal, and probably this forum software).

One node type would be 'location' and another 'people'.

Nodes are linked by many-to-many 'link' tables.

A location type node could be a table such as:

table location
locID
locType (eg 'Hosp', 'Entity', 'Clinic')
locName
...

The link table:

locationChild
parentLocID (eg the id for Hosp A)
childLocID (eg the id for Entity A)

For people:

table people
peopleID
FName
LName
...

Again, a link table:

locationPeople
locID
peopleID

In this schema, you can link any location to any other, or even connect one loc to many others - although I'm sure you wouldn't want that.

Similarly, a person can be linked to one or many locations. This may be desirable, eg a Dr who operates at Clinic A and Clinic B, or a Registrar who is linked to a Hosp, but also spends some time in a Clinic.

HTH

Max Hugen
Australia

 

[maxhugen]

The above does require that you're comfortable with self-linked tables, eg, table 'location' is linked to itself via the link table 'locationChild'. This object-orientated approach might be a bit daunting for developers more used to relational schemas.

The key consideration is to have only one people table, of course. An alternative is:

table hospital
hospitalID
hospitalName

table entity
hospitalID (Foreign Key)
entityID (Primary Key)
entityName

table clinic
entityID (Foreign Key)
clinicID (Primary Key)
clinicName

table people
peopleID
FName
LName

Now a linking table to link people to locations:

peopleLocation
peopleID
hospitalID
entityID
clinicID

In this linking table, the peopleID and only one of Hosp/Entity/Clinic ID fields is used. Again, you can link people to multiple locations if required.


Max Hugen
Australia

 

[SCantoria]

What if I describe the requirement this way:

An Organization can have zero or many organizations in it.
A Person can be a member of one or many Organizations.

Does this help?

 

[PHV]

Have a look here:

http://r937.com/relational.html

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886