should I include the customer ID as a primary foreign key or just a foreign key?
It can't be a "primary foreign key" because a PRIMARY KEY
is always UNIQUE
(and NOT NULL
) and presumably a customer can check in again, perhaps at a different day or time.
A PRIMARY FOREIGN KEY
is, in my opinion, an anti-pattern in all cases where the table you're referencing is not a compound key. It means there is a strict 0-1:1 relationship, just like all NULLABLE
columns. And in all cases, in most databases, it's better to put such relationships on the original table in columns and let them DEFAULT
to NULL
.
CREATE TABLE foo ( a int, b int );
Is perfectly fine. If b
is not there or applicable, you can set it to NULL
(or let it default to that).
CREATE TABLE foo ( a int PRIMARY KEY );
CREATE TABLE bar ( a int PRIMARY KEY REFERENCES foo, b int );
Is a bit silly. The only time I can see this making sense is something like this,
CREATE TABLE foo (a int PRIMARY KEY);
CREATE TABLE bar (a int REFERENCES TABLE foo, b int, PRIMARY KEY (a,b) );
In the above case bar
has a compound primary key of which a
is a constituent that happens to reference table foo
.