Unanswered: How to know the relationships in the relationship tables
Hello, I am currently doing a school project on a relational database for a person who owns a car repair business.
The first table i have contains the customer details. The fields are Customer number, surname, first name, address, suburb, postcode, and phone.
The second table was the car details, containing surname, car registration number, make, and model.
The third table is like a receipt table, it shows the date of the repair, the customer's name, registration number, labour costs, part costs, and the receipt number. The receipt number is only given if customer is paid.
I think the primary keys are Customer number from the first table, and the registration number being the primary key in the second table linking to registration number in the receipt table.
What i'm not sure is what to link the first table to. Should i replace the surname with the customer number so that i can link the customer number from the first table to the second one?
Yes, cust# should be the link, to link tCust.cust# to tRegistr.cust#.
But cust# should NOT be the primary key in tRegistr.cust#...it should be INDEXED.
Otherwise each customer may only register 1 car and never any more.
OR you can KEY 2 fields... tRegistr.cust# and tRegistr.VIN.
This way customers can have > 1 car but never the same car twice.
The third table (receipt), which is a Junction table, should have 2 Foreign Key columns: 1 referencing the Primary Key of the customer table and one referencing the Primary Key of the car table. Both columns should be indexed but allow duplicates values as a customer and a vehicule can have more than 1 entry in the receipt table (At least I hope so for the owner of the car repair business).
How you select the Primary Key of the receipt table depends on what the application will work as well as on the business model. You can:
- Create a compound PK associating the PK of the 2 other tables + the receipt number. This implies that as you wrote that the receipt number is only allocated when customer has paid, you cannot accept a service for a customer that already has an unpaid service.
- If this is not convenient, you can possibly associate the PK of both related tables with the date column. Now a customer cannot have the same vehicle serviced twice on the same day.
- Change the model and use an Autonumber for the receipt number. It then becomes the evident primary key.
- If you cannot use an Autonumber for the receipt number, nothing prevents you from adding an Autonumber column (RowID, for instance). Now RowID can be used as the primary key.
- Don't define a Primary Key at all for the receipt table. Although I consider that every table should have a PK, this would not make a big difference in this case as both FK columns are indexed and that almost all operations in the application will probably access the data from one of the related table (customer and car) in a Join query while both tables have a PK.
Personally I would use an extra Autonumber column (RowID) as the Primary Key.
Ok, i think i understand.
So i should create a new auto-number field in my receipt table and make it my primary key.
Then replace the [customer names] in the receipt table with the [customer number].
After that i should link the [customer number] from the customer table to the [customer number] in the receipt table.
Then the [registration number] from the car table to the [registration number] in the receipt table.
And finally i need to index and allow duplicate values for the [registration number] and [customer number] in the receipt table
Sweet, thank you very much for the quick and clear answers!
Edit: i have one more question actually. When i joined my tables in the query design it's not a one to many relationship. Is this because i did something wrong or does it just not show the 1 and infinite sign?
When you right click in the line, it thickens and a popup menu appears (the trick is that it's not always easy to catch the line, though). In the popup menu, chose Properties. A dialog window the opens that propose various types of relationships.
Is it the join properties? Where it has 4 drop down boxes and 3 option buttons and the first one saying "1:only include rows where the joined fields from both table are equal"
Because that's the only one that keeps popping up
Oh, but i've tried clicking the option buttons but they don't seem to change the relationship to many to many or one to many. All it does is put an arrow on the line. So i don't understand how to change the relationship still. Sorry if it's really obvious or something, but i'm quite new to relational databases.
Edit: Oh wait, i found it, all i had to do was right click the grey background, not the line. It must be different in different versions of access. Thank you for the assistance! Btw, should i enforce the referential integrity?
You cannot create a many to many relationship between to tables, you need to use a junction table that has a one (primary key) to many (foreign key) relationship with each tables. This is clearly explained in the document for which a provided an addrsss, did you read it?