3

In a Sales Order Header/ Detail scenario, should the details table have an arbitrary primary key (identity/guid) or should it be made up of the SalesOrderHeaderID and ProductID columns?

This is a general question for a Master/ Detail scenario where it's possible to create a composite unique key in the detail table with 2 or 3 rows. (In the above example we assume that an order cannot contain duplicate products).

Jules
  • 4,319
  • 3
  • 44
  • 72

5 Answers5

1

If you choose a natural composite key, and your design later changes so that your Order Details becomes the primary key for another table (for example, shipments), then you have to propogate that composite key to the new table. For that reason alone, I would choose an arbitrary identity as the primary key.

Also, this allows for the future situation where you can contain duplicate products on an order

(The answer to this question is of course an ecumenical matter with no correct answer)

podiluska
  • 50,950
  • 7
  • 98
  • 104
0

I would choose a natural primary key over an arbitrary primary key of identity/guid. If you are able to make the entries unique using a natural key then I would go that route.

So your basic table structure would be similar to this:

create table order
(
  order_id int -- PK
  order_date datetime,
  orderedby varchar(50)
);

create table products
(
  productid int, -- PK
  name varchar(50)
;

create table orderdetails
(
  order_id int,  -- PK/FK
  productId int  -- PK/FK
);
Taryn
  • 242,637
  • 56
  • 362
  • 405
0

I would have the identity column and the composite keys in the table.

The design would look like:

  • SalesOrderDetailId (int, Identity)
  • SalesOrderHeaderID (int, Foreign Key)
  • ProductID (int, Foreign Key)
  • Other fields required.

The composite keys are still used from both tables, however I find it consistent and useful for the composite table to have it's own identity (which to me personally makes it less confusing) when selecting from it.

Darren
  • 68,902
  • 24
  • 138
  • 144
0

In the scenario above, the details table should have the primary key made up of the SalesOrderHeaderID and ProductID columns - you are going to need the SalesOrderHeaderID stored and indexed on the details table anyway, so why not use it as part of the primary key?

In practice, it would be normal to use a combination of SalesOrderHeaderID and order line number as the primary key, as there are often business reasons for wanting to be able to record multiple order lines for the same product on the same order.

0

...should it be made up of the SalesOrderHeaderID and ProductID columns?

If you gave a complete description of the problem, then yes.

However, there may be other criteria you have not mentioned, that could skew the balance in favor of an additional, "surrogate" key.

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167