I am writing a new app with Rails so I have an id column on every table. What is the best practice for enforcing domain constraints using foreign keys? I'll outline my thoughts and frustration.
Here's what I would imagine as "The Rails Way". It's what I started with.
Companies:
id: integer, serial
company_code: char, unique, not null
Invoices:
id: integer, serial
company_id: integer, not null
Products:
id: integer, serial
sku: char, unique, not null
company_id: integer, not null
LineItems:
id: integer, serial
invoice_id: integer, not null, references Invoices (id)
product_id: integer, not null, references Products (id)
The problem with this is that a product from one company might appear on an invoice for a different company. I added a (company_id: integer, not null) to LineItems, sort of like I'd do if only using natural keys and serials, then added a composite foreign key.
LineItems (product_id, company_id) references Products (id, company_id)
LineItems (invoice_id, company_id) references Invoices (id, company_id)
This properly constrains LineItems to a single company but it seems over-engineered and wrong. company_id in LineItems is extraneous because the surrogate foreign keys are already unique in the foreign table. Postgres requires that I add a unique index for the referenced attributes so I am creating a unique index on (id, company_id) in Products and Invoices, even though id is simply unique.
The following table with natural keys and a serial invoice number would not have these issues.
LineItems:
company_code: char, not null
sku: char, not null
invoice_id: integer, not null
I can ignore the surrogate keys in the LineItems table but this also seems wrong. Why make the database join on char when it has an integer already there to use? Also, doing it exactly like the above would require me to add company_code, a natural foreign key, to Products and Invoices.
The compromise...
LineItems:
company_id: integer, not null
sku: integer, not null
invoice_id: integer, not null
does not require natural foreign keys in other tables but it is still joining on char when there is a integer available.
Is there a clean way to enforce domain constraints with foreign keys like God intended, but in the presence of surrogates, without turning the schema and indexes into a complicated mess?
