Help With First attempt at Database Relationships

[Renaldo]

Hi guys!
I’m quite new to access so please bear with me.
What I want to do is make a database for my father’s engineering company.
What I need is as follows:

A Customer places an order for a project. (So I thought I’d need a Customers
table and a Projects table)
The project can have many different jobs that need doing. (Jobs table)
Each of these jobs can be further broken down into Labour on the job,
Materials used and any additional items or costs. (Labour table, Materials
table and Items table)
Under Labour any number of machines can be used to do the jobs. (Machines
table)
Under Materials, any number of suppliers could be used to supply the
materials (Suppliers table)

My table setup so far looks as follows:

[Customers]
Customer ID
Company name
Contact First Name
Contact Last Name
Telephone
Email

[Projects]
Project ID
Project Name
Order Date
Completion Date
Project Cost
Customer ID

[Jobs]
Job ID
Job Name
Job Description
Job Quantity
Job Cost
Project ID

[Labour]
Labour ID
Labour Description
Labour Quantity
Labour Hours
Labour Cost
Job ID

[Machinery]
Machinery ID
Machine
Machine Rate

[Materials]
Materials ID
Description
Materials Quantity
Materials Unit Price
Materials Total Cost
Job ID
Supplier ID

[Suppliers]
Supplier ID
Company
Contact Person First Name
Contact Person Last Name
Telephone
Email

[Items]
Item ID
Description
Price
Items Total Cost
Job ID

Now my relationships setup is a bit fuzzy. I figured as follows:

Customers – one to many – Projects
Projects – one to many – Jobs

Now here is where I get lost. One job can have only one Materials and only
one Items and only one Labour.

Jobs – one to one - Labour
Jobs – one to one – Materials
Jobs – one to one – Items

Labour can have many machines working. And one machine can work on many
different labours. (All the different jobs.)
One piece material can have many different suppliers (depends on who is
available or cheapest at the time) and one supplier can supply many different
materials.
How do I set up those relationships? A many to many system is needed I
think, but I can’t figure out how to implement it. Are there perhaps any
other errors or things I skipped that you can help with?
Thanks a lot guys!
Renaldo

 

[Stephen Raftery]

To set up a Many-Many relationship, you need to create an intermediate table.
So if One piece material can have many different suppliers (depends on who is
available or cheapest at the time) and one supplier can supply many different
materials, what you need is another table:

SupplerMaterials
-SupplierID
-MaterialID
-AnyOtherInfoNeeded


Stephen