0

I would like to know what is the write design about join table to respect nf norms please ? I have read the nf norms but I can't find clear specific rules about it.

For exemple, if I want to link a table containing "persons" and a table containing "cars", is the following design valid please?

persons
id
firstname
lastname
cars
id
licence_plate
model
cars*persons
car_id
person_id
amount
date_buy
2
  • It depends on your use case - can a car have more than one Person associated with it? Are you collecting info about Cars without the associated owner info?
    – user212533
    Commented Dec 1, 2022 at 16:09
  • Thank you for your comment ! I fact I think I didn't ask my question clearly. I already know that this representation is valid to the use case I want. But I wanted to know if the representation was valid regarding all the NF norms please ?
    – shugyosha
    Commented Dec 2, 2022 at 9:45

1 Answer 1

2

Yes, your design is good if a person can have many cars and a car can belong to many people.

A couple notes on your naming conventions: the style I most often see and have adopted myself is to use PascalCase for table and column names, and full names for ID columns. I also plural tense for standard tables and singular tense with underscores for many-to-many join tables.

Example:

People
PersonId
FirstName
LastName
Cars
CarId
LicencePlate
Model
Person_Car
PersonId
CarId
Amount
PurchaseDate

A couple of benefits when querying against these tables:

  • It is clear which Id is which in your join. c.CarId = cp.CarId is more clear than c.Id = cp.CarId and less likely to be accidentally joined on the wrong column (which I've definitely done)
  • You can tell visually that a join is a many-to-many by 2 entity names separated by an underscore.

Hope this helps,

Scott

4
  • Well put Scott. Especially your first point about easily seeing the join ids.
    – Muab Nhoj
    Commented Dec 1, 2022 at 21:06
  • Thank you very much for your so good answers ! I will change the naming conventions as you mention. However, I wanted to be sure that this design is valid by every NF norms please ?
    – shugyosha
    Commented Dec 2, 2022 at 9:43
  • @shugyosha I'm not sure what you mean by "every NF norms". Can you provide a definition or page for me to read? To the best of my knowledge, the design conforms to 2nd, 3rd, and Boyce-Codd normal form. Commented Dec 5, 2022 at 17:51
  • @ScottMcCabe thank you for your answer ! Please find in thelink below the definitions of differents norms that I read before. I'm just not sure to fully understand them and so, I'm not sure that my design is fully compliant to them. https://en.wikipedia.org/wiki/Database_normalization
    – shugyosha
    Commented Dec 7, 2022 at 10:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.