Initial Table Design & Relationships

[Saelin]

Hello Everyone,

I am new to the forums and you can check my intro thread to read a little about me and my background in that introductory post (can't give you the hyperlink as I am under 10 posts ). Anyway, I am creating a database (version 2007, if it matters) that will house:

· Cardiologists
· The Facilities they work
· The services they provide
· The counties where the facilities are located

As such, these are the tables I believe I should have:

· Physician (Cardiologist)
· Facility
· County
· Service


As I am still understanding DB design “in the real world” I would like confirmation on the creation of the tables and the relationships they have. I have attached a screenshot of the tables (though I haven’t designated the relationships yet). So I want to know what you think of the table design? Also, about Foreign Keys. A physician will typically have more than one service. So should each Service in the Physician Table (as a Foreign Key) be linked to the Service Table? Thanks in advance.


A down the road question: I have read that the ID Number of a table should be the primary key even though it is easier to understand the DB if I could use the actual name that the ID refers to. How do I get around that? And please feel free to nitpick anything (like naming conventions and what-not) or general tips. I am here to learn from the more experienced so I will gladly accept any suggestions and constructive criticism.

 

[cookiegary01]

Hi Saelin,

I would relate 2 tables to your Physician table :Facility and Service.
So in Tbl Physician I would delete FAC_1 till FAC_3 and Serv_name1 till Serv_Name3.
Instead add IdFAC and IdService. These new fields must be Numeric / Long integer.
Then make relationships "1 to many":
- FAC_Id in Facility with IdFAC in Table Physician
- Serv_id in Table Service with IdService in table Physician.
Once you have created a FormPhysician, you can then add Facility and Service as 2 SubForms for example.

Furthermore I would make arelationship between Country and Service if they are linked.

 

[plog]

The first thing I notice is all your fields that have similar names and ending with different numbers. This is usually a sign that your data isn't properly normalized and is true in your case.

In the Physicians table, instead of 'Fac_1', 'Fac_2', etc., you should create a new table called something like PhysiciansFacilities which has 2 numeric fields. One of those fields holds the Card_ID value of a physician and the other field holds the Fac_ID of a facility. Your PhysiciansFacilities table would then look like this:

ID_Card, ID_Fac
1,1
1,2
2,1
2,4
2,3
2,5

Which translates into Physician with the ID of 1 working at Facilities 1 and 2; and Physician with the ID of 2 working at Facilities 1,3,4 and 5. Your method would require you to add a new field (i.e. 'Fac_7', 'Fac_8') to your Physicians table accomodate the physician who works at the most facilites even though most physicians only work at a lesser number.

The same principle applies to all your 'Serv_Name' fields--you need a table like 'PhysicianServices' with just 2 numeric fields.

Most likely the same applies to the 'Days_Oper' and 'Hours_Oper' fields in the Facility table as well.

This normalization technique is also why we use ID numbers and not names as foreign keys. If you were to create the PhysiciansFacilities table using names you would be in for a ton of work if a facility changed its name or a doctor changed theirs--each record of the PhysiciansFacilites would have to be edited. Using numbers allows you to link to the Facility table and Physicians table and retrieve whatever name is associated with that id--if a change is needed you only change the record in the Facility or Physician table.

 

[Saelin]

Thanks for the replies, Gary & Plog. I see two different ways of doing the same thing, which is fine, but I'll have to choose...but I also want to make this a solid learning experience so I have some questions for each of you:

Hi Saelin,

I would relate 2 tables to your Physician table :Facility and Service.
So in Tbl Physician I would delete FAC_1 till FAC_3 and Serv_name1 till Serv_Name3.
Instead add IdFAC and IdService. These new fields must be Numeric / Long integer.
Then make relationships "1 to many":
- FAC_Id in Facility with IdFAC in Table Physician
- Serv_id in Table Service with IdService in table Physician.
Once you have created a FormPhysician, you can then add Facility and Service as 2 SubForms for example.

Furthermore I would make arelationship between Country and Service if they are linked.

So I could then with the subforms add as many services as is necessary to the physician table due to the "1 to many" relationship I have created, correct?


*****

The same principle applies to all your 'Serv_Name' fields--you need a table like 'PhysicianServices' with just 2 numeric fields.

Most likely the same applies to the 'Days_Oper' and 'Hours_Oper' fields in the Facility table as well.

So the general principle I am seeing here is, if a table will have more than one entry of the same thing (like a Physician will have more than one service), I should make a separate table? And the PK is just an Autonumber for its own sake? But I don't see the relationship with other tables, as you're using just foreign keys for the entry (composite key, perhaps?) This sounds like a principle for databases in general while Gary's post was geared specifically with using Access. Is this a correct interpretation? I would like to put both of your responses in perspective.

This normalization technique is also why we use ID numbers and not names as foreign keys...Using numbers allows you to link to the Facility table and Physicians table and retrieve whatever name is associated with that id--if a change is needed you only change the record in the Facility or Physician table.

I definitely understand that, and so what I am asking if there is a way that it is linked to the ID for back-end purposes, but what is viewed is the Name (on the eventual form) for ease of use. It won't bother me much now as I am doing the entry but I can foresee in the future when I create a DB that I won't be doing the data entry and it would be easier for users to see the name on the form rather than the ID.

 

[plog]

Gary and I are on the same page regardless of the database system you are using. Instead of numbered fields in a table you should have a new table(s). Those new tables don't really need an autonumber primary key, but you can add one if you like. The main thing is to have the unique id numbers from the 2 tables you are putting together in there.

When you display your data (in a form or report) there is no need to show the ID numbers. However they are necessary for proper normalization.

 

[Saelin]

I mentioned the autonumber for the sake of a primary key even though it's not needed for practical purposes. Forgive my n00bness, but it would be ok to not set a primary key for these new tables as long (as you said), that the each record is a unique combination of the ID numbers. And then I'll set a 1 to many relationship where the "1" is the original tables and "many" would be the the new tables I would create?

So I'm thinking I'll create Facility and Service subforms within Physician, and create a new FacOps Table that covers the Facility Operation days and times. What do you think?

I appreciate the patience.

 

[cookiegary01]

Your Question1: "So I could then with the subforms add as many services as is necessary to the physician table due to the "1 to many" relationship I have created, correct?"
My Answer: Yes, correct.
Your Question 2:"If a table will have more than one entry of the same thing (like a Physician will have more than one service), I should make a separate table?"
My answer: Correct again.

See it like a client, that has several delivery addresses.
If you link delivery address to the client as described (1 to many), you can enter infinite delivery addresses.

 

[Saelin]

I'll work on the DB in the next couple of days and will provide feedback. Thanks again.

 

[Saelin]

Update: Initial Table Design & Relationships

So attached is the updated relationships. Ignore the DaysOP_ID & HoursOP_ID since I haven't gotten to correcting that aspect yet of normalizing the database.

To Recap, I am creating a Cardiologist Database with specific "Facilities", "Counties", and "Services", of which many Cardiologists overlap in all 3 circumstances. FYI, the "conditions" are symptoms/procedures needed that's related to the "Service". This is my first database, so I have another n00b question:

When I try to run a report of Cardiologists by Counties, I get duplicates since 1 cardiologist may be in multiple facilities. If I want to remove the duplicates, do I then have to create another table (Let's say "tblCountiesCardiologists") to get the desired result or is there another way? I just want to make sure I am not doing extra work, because there is an indirect relationship there already and I want to clarify do I always need to make a new table to make a direct relationship or can I save some time and just modify the report/query to clean up duplicates.

I am still learning the ropes on database design principles so forgive in advance if any of this should be obvious. Thanks.

-Stewart

 

[cookiegary01]

I see quite a lot of setup problems.
1: Do not relate 2 Autonumbers.
2: You should have 1 Autonumber per table.
3: Don't use the same name "FAC_ID" for autonumber in different tables
4: Make less tables if possible
5: In TblFacilitiesCardiologist why Card_ID?
6: In TblServicesCardiologist why Card_ID?

TIP: Work it out on paper first:
Draw Tbl Cardiologist as the "Centre table" and make as many relationships to this table as possible.
Focus on 1 Cardiologist:
TblCardiologist will contain: SurnameCardio;FirstNameCardio;etc.
This cardiologist can have several ID cards, so make a related table to Cardiologist, where you can add many ID Card records for him/her.
He/she works at several facilities: so make a related table to Cardiologist, where you can add many Facility records for him/her.
The relationship TblFacilities and TblCountries is good, its at the end of the chain from TblCardiologists point of view.

 

[cookiegary01]

Regarding your question about duplicates.
Like you said, you get duplicates since 1 cardiologist may be in multiple facilities (which is correct). To solve this you can make a report based on the query, and there you will group by Cardiologist and Country.

 

[Saelin]

Thanks for responding Gary,

I see quite a lot of setup problems.
1: Do not relate 2 Autonumbers.
2: You should have 1 Autonumber per table.
3: Don't use the same name "FAC_ID" for autonumber in different tables

If I understand what you're saying correctly I actually haven't related 2 Autonumbers. This is the technique that Plog was talking about regarding breaking down the tables. The tables that have double primary keys are just Foreign Keys that are Long Integers. The reason I chose to do a double primary key was to safe guard that I wasn't duplicating my entried by mistake. So "Fac_ID" is an autonumber only in the Facilities table, and "Card_ID" is only an autonumber in the Cardiologists table, etc.

4: Make less tables if possible
5: In TblFacilitiesCardiologist why Card_ID?
6: In TblServicesCardiologist why Card_ID?

Draw Tbl Cardiologist as the "Centre table" and make as many relationships to this table as possible.

The reason for the multiple tables is due to the fact that I have few natural One-Many Relationships. For Cardiologists specifically, they don't have any One-Many Relationships:

• Many Cardiologists work many multiple facilities
• Many Cardiologists work in many multiple counties
• Many Cardiologists render many multiple services
• Many Cardiologists can treat many multiple conditions (no table created yet for that)

Because of this, that's why the TblFacilitiesCardiologist and TblServicesCardiologist exist, because I eventually will have to relate those two entities directly for a report I will have to generate. I haven't used subforms (because I am not up to that in my learning of Access ), but it would be the same difference I suspect...or I could be wrong.

Focus on 1 Cardiologist:
TblCardiologist will contain: SurnameCardio;FirstNameCardio;etc.
This cardiologist can have several ID cards, so make a related table to Cardiologist, where you can add many ID Card records for him/her.
He/she works at several facilities: so make a related table to Cardiologist, where you can add many Facility records for him/her.

Thanks for that tip on focusing on 1 cardiologist, btw. The latter part of this I guess is where I haven't gotten my head wrapped around. I understand the relationship, but how can I add many Facility Records of the Cardiologist with the redundancy on both sides. So with Plog's method, which I believe I followed, I am able to break that down. The database works out well so far, but there is just a lot of data entry. If that's the reality, I'm fine with that, but I wanted to make sure that I am not overdoing it.

 

[cookiegary01]

Hi,
Ok then, if works fine for you then that's great, but this would be my next suggestion:

1:tblServicesCardiologist and TblServices are the same thing, so you could bring it back to 1 table?
2: For the same reason TblServicesConditions could also be deleted.... and relate TblConditions with TblServices?
3:The same story for TblFacilitiesCardiologist (I think this is also 1 table too many)

I don't think youre overdoing it.

As for maintaining the tables:
TblServices;TblConditions;TblFacilities and TblCountries are a "one time entry" (selection data). Once the data is in there, hardly any maintaining required.

If you do it as i suggested above:
The trick is, everytime you add a record for any Cardiologist in a "data entry form"... You want to select from the tables directly related to Cardiologist... 1 entry for Facility and one entry for Services would be smart.
How do you do that?
TblFacility: add a text field "Country". (Everytime you select a country from the TblCountries you will program the Form in such a way, that it will automatically add the country text selected to the new field).
Do the same for TblServices, add a text field "Condition".

 

[Saelin]

Hi,
If you do it as i suggested above:
The trick is, everytime you add a record for any Cardiologist in a "data entry form"... You want to select from the tables directly related to Cardiologist... 1 entry for Facility and one entry for Services would be smart.

Definitely agree, and that's what I have been trying to work around with my limited knowledge. I was getting concerned about future data entries or converting all of this to a form eventually...which leads to:

How do you do that?
TblFacility: add a text field "Country". (Everytime you select a country from the TblCountries you will program the Form in such a way, that it will automatically add the country text selected to the new field).
Do the same for TblServices, add a text field "Condition".

I don't expect you to tell me how to do this step by step but what area is this so I can look up how to do this or what am I setting up exactly to do this. I understand the concept, but the execution of this is another matter. But then again, what about multiple entries of the same thing? These are where the subforms come in? Is this unique to Access? Because if so, I think this may be why I seem to go around in circles with what I learned in school about general databases, and what Access can do that can possibly simplify the process.

 

[cookiegary01]

I like you initiative.
Got to (on this forum): Video tutorials,Forms;Creating a main form and sub form using the Form Wizard.
This would be how you set up TblCardiologist (Main form) + TblServices (Sub form) and (as an extra Subform) TblFacility.
Look and learn and try it out in practice.
The subforms come in to add multiple records for 1 Cardiologist.
You'll love it finally, when it works.

 

[Saelin]

I will try it out within the next 48 hours as of course, I have other responsibilities here at work. Again, I sincerely appreciate your time spent and patience. I already see how much easier this database will be to create. I'll keep you posted.