Linking multiple tables to one

[jlt199]

Hi, I have a database with three different groups of people: Physicians, Patients and Facilitators. I have a table for each. However, it's possible that any of these people can have multiple phone numbers. So I've created a Table (PhoneID | PersonID | PhoneNumber) where personID is either PhysicianID, PatientID or FacilitatorID - i.e. one table holds all the phone numbers.



The ID for Physicians, Patients and Facilitators are all generated using autonumber. I had though that by changing the format of PatientID to "Pt"0 I could give the IDs from each table a unique prefix, ensuring that all PersonIDs are unique to a single person. But that isn't doing what I thought it was. It only changes the display, not the actual field. Please can someone suggest a method of making sure PersonID can relate to one person out of the combined Physicians, Patients and Facilitators? OR should I have three tables for phone numbers, three for emails, three for faxes etc.?

Many thanks

[Xipooo]

I see a problem with the way you are trying to relate your data. Use the primary key of the phone table as the foreign key on each of your other tables.

Phones
-ID (Primary Key)
-PhoneNumber

Physicians
-ID (Primary Key)
-Name
-PhoneID (Foreign Key to Phones table)

Patients
-ID (Primary Key)
-Name
-PhoneID (Foreign Key to Phones table)

Facilitators
-ID (Primary Key)
-Name
-PhoneID (Foreign Key to Phones table)

[ItsMe]

Formatting does not, in most every case, have an effect on the data in the table. You now know this. So, use the table you created to store the literal Autonumber (Primary Key) value in the PersonID field.

You may be able to manage this third table using Joins and intrinsic referential integrity rules. My preference is to use VBA.

[jlt199]

Xipooo: That would result in only being able to assign one phone number per patient record, would it not?

ItsMe: I have no idea what your recommendation was , I'm new to Access and trying to muddle my way through, your suggestion does not look straight-forward for a novice

Being as I'm new and fairly time restricted should I just make separate tables for each type of person? Another thought, is there a way to make sure that any assigned autonumber is unique to all tables in the database?

Thanks

[Xipooo]

Xipooo: That would result in only being able to assign one phone number per patient record, would it not?

Thanks

If you are looking to build a Many-To-Many relationship then you use a third table.

Phones
-ID
-Name

Patients
-ID
-Name

Patients_Phones
-PatientID
-PhoneID

You can either make separate joining tables, one for a patient, one for Physicians, and one for Facilitators or you can make one table with all of the joining information in 4 fields.

People_Phones
-PhoneID
-PhysicianID
-PatientID
-FacilitatorID

The downside of a single table holding all of your linked data is that you'll have 2 null fields for every row.

[jlt199]

Thanks Xipooo

Would using a composite key work? I'm currently testing this to see if I can make it work.

I've added an extra field in the Patients Table, which holds the abbreviation for the table "Pt" in every record. Then in design view selected both TblId and PatientID (using Ctrl) and made them a primary key.

I've made a relationship to the PhoneNumber table and it seems to be doing what I want it to. Should it do what I want, or is there a reason not to do this?



Thanks

[jlt199]

Nevermind, I see my suggestion isn't going to work.

What's the argument between using one table for all phone numbers with a separate linking table to each of the different types of people as opposed to have a table for all patient phone numbers, one for all physician numbers and one for all facilitators?

One gives you a smaller number of overall tables, is that ever a consideration? IS one method more efficient?

Many thanks

[ItsMe]

I won't have a chance to look at it today but, if you want to submit your DB for review, remove personal data and upload it here.

[Xipooo]

Typically I would write a single table with all of my phone numbers, a separate table for each group of people, and then a separate linking table for each group of people. This will keep things a bit more organized and slimmed down in your database.

[jlt199]

Thank you for your responses, I've taken a slightly different route and added a new "People" table that contains all patients, physicians and facilitators. Now everyone in the database has a unique ID and I don't have the problem above.

Another question though. How much of your data validation do you normally do in the actual tables and how much in the forms? For example, in this database I'm booking patients onto a set of classes. The series is made up of four classes i, ii, iii, iv. To make sure a patient is booked onto one of each of these classes would you set up data validation of the tables and/or build the forms in such a way that that's all patients can be booked on?

Thanks

[ItsMe]

Keep your tables pristine. Hold the user input, queries in memory and update, append tables after validation.

[Xipooo]

To add to what ItsMe said, validate the data AND set up your tables to have as specific of data requirements as you can. The more validation the better.