Linking table

[TDSRU]

I have a database with the following tables (among others):

tbl_Projects
tbl_Staff
tbl_ProjectsStaff
tbl_Payroll
tbl_Training
tbl_Clients
tbl_ProjectClients
tbl_ClientContracts
tbl_CostEstimates

As you can see I created two linking tables (project/staff and project/client)



After that I linked other tables to the liking table. For example, tbl_Payroll and tbl_Training are linked to the tbl_ProjectStaff primary key.

I don't know much about access so I wanted to check if this is a correct design. I create forms that work well, but I noticed that the linking table doesn't have any data in it. The payroll table and the training table have the staff iDs and Project IDs but the tbl_ProjectStaff is empty. I would appreciate any feedback on the database design.

[pbaldy]

Based on the names, the design seems correct. The "linking" tables are often referred to as junction tables.

[TDSRU]

Thank you for your reply.

What is the function of the junction table if it is empty?

Thanks

[pbaldy]

Nothing. It's function, like any table's, is to hold data. It wouldn't have data until you put it there, via a form, etc. Were you expecting it to populate itself?

[TDSRU]

I don't plan to enter data on this table, I was just using to create the many-to-many relationship. But maybe I don't need this table. Maybe the payroll and training table would have this function of creating the many-to-many relationship between projects and staff. Can I have several junction tables (e.g. Payroll, training, etc)?

[pbaldy]

The junction table would hold the data of the many-to-many relationship. For instance if project A had 3 staff members, that table would have 3 records, each with the key for project A and one of the staff keys.

[TDSRU]

But right now I do that with the payroll table, training table, banked hours table, etc. Can I have more than one junction table for the same junction (staff-project)?

[pbaldy]

How would any of those hold the combinations of projects and staff? They each sound like they contain other information. What are their fields?

[TDSRU]

Payroll for example has task, hour, coat centre, etc +

[TDSRU]

Sorry for the other message. I sent it by mistake before finishing it.

Payroll for example has task, hour, cost centre etc + staff_ID, Project_ID, Staff-Project_ID and Payroll_ID.

[pbaldy]

That's along the lines of what I would expect, but is NOT the project/staff info. Perhaps you don't need or want the table, but it's purpose is what I described in post 6.

[pbaldy]

You added an ID field from the staff/project table, but if you don't populate it where is that ID coming from?

[TDSRU]

It is the main ID of the staff/project table, but It is also empty.

[June7]

Many-to-many relationship requires junction table. Review http://office.microsoft.com/en-us/ac...aspx?section=9

[TDSRU]

Should I have 1 junction table for each set of topics (e.g. Payroll, training, etc) or should I make a huge junction table with everything?