Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    May 2014
    Posts
    14

    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?

    Thank you very much in advance

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    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.
    (follow?)

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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.
    Have a nice day!

  4. #4
    Join Date
    May 2014
    Posts
    14
    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

    Is this correct?

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by bluesaber27 View Post
    So i should create a new auto-number field in my receipt table and make it my primary key.
    This is one of the options you have. I described several other solutions.
    Quote Originally Posted by bluesaber27 View Post
    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

    Is this correct?
    That's it, exactly. For more details on how to use junction tables, see: Junction Tables (Many-to-Many Relationships - Access wiki - Access Help and How-to - Microsoft Office by UtterAccess.com)
    Have a nice day!

  6. #6
    Join Date
    May 2014
    Posts
    14
    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?
    Last edited by bluesaber27; 05-10-14 at 05:12.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!

    When you define a relationship between to tables, you can right-click on the line that represents the relationship (the link) then select the kind or relationship you want to define.
    Have a nice day!

  8. #8
    Join Date
    May 2014
    Posts
    14
    I've right clicked the line but i don't seem to see how to do it. Does it matter if i'm using Microsoft access 2010?

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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.
    Have a nice day!

  10. #10
    Join Date
    May 2014
    Posts
    14
    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

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    That's it, precisely.
    Have a nice day!

  12. #12
    Join Date
    May 2014
    Posts
    14
    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?
    Last edited by bluesaber27; 05-10-14 at 10:10.

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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?
    Have a nice day!

  14. #14
    Join Date
    May 2014
    Posts
    14
    I did click on the link, but it says there's no text currently in this page

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Google "Junction table" or "many to many relationship". The link points to (remove the *): http:*//www*.utteraccess.*com/wiki/index.php/Junction_Tables_(Many-to-Many_Relationships
    Have a nice day!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •