Table Relationship Types (What determines?)

[dccjr]

I have a database project with 18+ tables in it. I am trying to setup the relationships, but am not sure about the best (appropriate?) structure. I have a table (IDInfo) with a field called WPSNo. Ten other tables also have this field. I am not sure whether to create the relationship One-to-many from the IDInfo table to the others, or one-to-one-to-one (cascading through the tables sequentially).



Functionally, I will need to pull a record based on WPSNo from each table for display as an on-screen report. (I am writing to them individually from a multi-tabbed form with seperate SQLs.)

Which is the correct (most efficient) way to relate these tables?

[ssanfu]

I am not sure whether to create the relationship One-to-many from the IDInfo table to the others, or one-to-one-to-one (cascading through the tables sequentially).

I think you misunderstand what the cascade options do.

I would set the relationships to One-to-Many, from the "IDInfo" table to the others.

Cascading updates will not "cascade through the tables sequentially".


See "Guide to table relationships" at
http://office.microsoft.com/en-us/ac...010120534.aspx
(Pay attention to the last line that begins with "NOTE".)


For example, lets say you have 3 tables: "Employees", "Training" and "Attendance".
The PK in the employees table is "EmpID_PK", a long Integer field, no duplicates (not an autonumber). The first employee will have an "EmpID_PK" of 1000.
The other 2 tables have autonumbers for the PK fields and a foreign key field name of "EmpID_FK" that links to the "Employees" table.

So the relationships are

Employees	Training
1	many
Employees	Attendance
1	many

Albert Aardvark is given the employee ID of 1000. And lets say there are 100 records in the training table for Albert and 85 record in the attendance table.
300 more employees are entered and each has 100 records in the training table and 85 record in the attendance table.

If Albert Aardvark loses his ID card and has to get a new employee ID issued (due to security reasons), you would have to locate and manually change 185 records from 1000 to his new number, 3000.
(Yes, you could use an update query, but this is my example; and you, as the user, only have access to forms.)

If you have cascading updates set to TRUE, when you change the number in the "EmpID_PK" field (in table "Employees"), the new number cascades to the other two tables.

Now Albert decides to quit. Without cascading deletes, you would have to locate and manually delete 185 records in two tables. With cascading deletes, you delete Albert's record from the "Employees" table and all of the records associated with Albert are also deleted.

[orange]

A good tutorial on table design and relationships is at
http://www.rogersaccesslibrary.com/T...lationship.zip