Simple example: there is a table of customers.
create table Customers (
id integer,
constraint CustomersPK primary key (id)
)
All the other data in the database should link to a Customer
, so e.g. Orders
looks like this:
create table Orders (
id integer,
customer integer,
constraint OrdersPK primary key (customer, id),
constraint OrdersFKCustomers foreign key (customer) references Customers (id)
)
Suppose now there is a table linking to Orders
:
create table Items (
id integer,
customer integer,
order integer,
constraint ItemsPK primary key (customer, id),
constraint ItemsFKOrders foreign key (customer, order) references Orders (customer, id)
)
Should I add a separate foreign key from Items
to Customers
?
...
constraint ItemsFKCustomers foreign key (customer) references Customers (id)
A picture instead: should I add the dashed line/FK?
Edit: I have added primary key definitions to the tables. I'd like to re-iterate on the point I made above: the database is basically siloed by customers, as a correctness / security measure. Therefore, all primary keys contain the customer
ID.
Best Answer
I think this is the original idea.
First thing to notice is that the PK on the LineItem table has three attributes
{CustomerID, CustomerOrderNo, OdrerItemNo}
, as opposed to just two in your example.Second thing to note is the confusion resulting from the use of the generic
id
name for an attribute.The
CustomerOrderNo
should ideally be (1,2,3..) for each customer andOrderItemNo
(1,2,3 ...) for each order.Well, this is nice if possible, but requires a query looking for the previous max value, like
which is often not preferred in high-transaction-volume environments, so it is common to see these replaced by an auto-increment, essentially serving the same purpose. It is true that this auto-incremet is now unique, hence it can be used as a KEY -- but you may choose to look at it as a necessary compromise for the
OrderItemNo
.So, with some renaming
CustomerOrderNo -> OrderNo
andOrderItemNo
->ItemNo
you may arrive to this modelSo now if you look at the
Order
the following are uniqueNote that
{CustomerID, OrderNo}
is propagated to theLineItem
to serve as a FK.If you squint a bit, this is close to your example, but with
PKs {ItemNo} and {OrderNo}
only -- as opposed to two column PKs from your example.Now the question is, why not simplify to something like this?
Which is fine, but introduces PATH DEPENDENCE -- you can not join
LineItem
withCustomer
directly, must useOrder
in the join.I prefer the first case when possible -- you choose your favourite. And obviously, there is no need for direct FK from
LineItem
toCustomer
in these three cases.