Hi,
I am creating a new database to store information on Jobs. The criteria are;
A Job has a Job # and a Job Name
When allocating jobs, the job is broken down into Divisions. Each job has many Divisions e.g.
Job_Number: 101
Job Name: Remodel the Kitchen
Divisions: Carpenters, Plumers, Tilers.
The divisions have numbers e.g.
Division_ID: 20, Division Name: Carpenters
Division_ID: 30, Division Name: Plumbers
Division_ID: 40, Division Name: Tilers
Each Division has Subdivisions e.g.
Division_ID: 20, Division Name: Carpenters
SubDivision_ID: 21, SubDivisionName: Drywall
SubDivision_ID: 22, SubDivisionName: Cabinet Installers
Division_ID: 30, Division Name: Plumbers
SubDivision_ID: 31, SubDivisionName: Pipe Work
SubDivision_ID: 32, SubDivisionName: Sink/Fixture Installers
I am having trouble setting up the relationship for these tables. The tables I have are
tbl Job Info
Job_Number
Job_Name
tbl Division Info
Division_ID
Division_Name
tbl SubDivision Info
Subdivision_ID
Subdivision_Name
I created another table called tbl Job Division Info where I pulled the PK's from tbl Job Info and tbl Division Info. So this table looks like:
tbl Job Division Info
Job_Number
Division_ID
I did this because one job can have many divisions and one division can be on many jobs. Is this correct?
My other problem is how do I relate tbl SubDivision Info to tbl Division Info ? I have attached what my relationship table looks like but to me I have Division_ID being related from one table to two other tables. Is this good practice or will totally not work?
Please can someone help me? I've posted before with no response at all, I hope I have enough information to get something back.
Thanks.