0

Hello I need a suggestion.

I have two examples.

Example 1 Example 2

In both cases I have one table for cars and one for orders. A car is defined over brand and model. An order has an order number and a car. I know, the order table is not well defined. But my problem is the car table. Which Example for the car table is better? The first one looks better. But whenever I use the table cars for join with e.g. orders, I have to use both columns. In the second example I have only one column (ID_Car), which I have to care about.

Which Case would you prefer? Could one way cause later problems?

If I use the example 2. Are the Primary Key’s and the unique statement right. Or is it better to define the Columns ID_Car as primary key and Unique(Brand, Model)?

Thank you guys

user3659692
  • 29
  • 4
  • As to which is better, even the top DBA's in the world can't agree on this one (Aaron Bertrand and Joe Celko spring to mind as commonly being on opposite sides of this argument). If these two cannot agree, then you are unlikely to get a concrete answer on here. There are probably entire chapters in books written about Surrogate vs Natural keys so again, you are unlikely to get a definitive answer here. – GarethD Jul 08 '16 at 12:21
  • Personally I pefer the an approach in which you use only one Integer column as primary key (With unique restriction for the other unique colums). Composed primary keys can be a mess when you have "intermediate tables" for your N.M relations and create difficulties in DML operations. – Cristian Abelleira Jul 08 '16 at 12:23
  • I would almost always choose the second option, and definitely if there is any doubt about the stability of one of the fields, if a car manufacturer rebrands, you would then need to update the primary key, and cascade it to all referencing tables. If there are numerous child tables too, then having to repeat a compound key in every child table will take much more space, and create wider indexes than a single integer column. Beyond that (and not the primary reason, but still a concern), it will become quite tedious typing out multiple columns every time you want to join child tables. – GarethD Jul 08 '16 at 12:36
  • IMHO the second option is better and if possibile I'll use a third option using another table for brand description (just take into account that you may have to store additional data for each brand). Anyway I agree with GarethD about overall philosophy.. there's not a definitive answer. – weirdgyn Jul 08 '16 at 12:42

0 Answers0