If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help With First attempt at Database Relationships
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 [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 [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 |
Thread Tools | |
Display Modes | |
|
|