How should I define a composite foreign key for domain constraints in the presence of surrogate keys?

Question

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 this added complexity because the referenced columns are already natural keys so they already have a unique index.

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?

Answer 1

You mention id is already unique above, but you have to make id, company_id unique because you are are using it in a FK reference that needs to make sure it only reference 1 unique item and not anything else ever.

I understand you want to 'protect' the data but I would say this is over-engineered and agree with you. Any system that uses this data and does not work properly (pulling up the correct products for a company) will be immediately noticeable. It will put a burden of dealing with more data when programming to and might make things more confusing.

I like the effort to protect the data, but the cost could grow to be way to high as you get more data and there is definitely the added burden of a more complex data model.

I think the data is protected enough through 'natural' use in the app. When you go to create a new invoice and you are looking for products to put on the invoice you are going to be using the same company id to search for products as you are using to create the invoice. If you somehow get the wrong ids in there (bad code, users directly in DB) then the bad results will be noticeable right away.

I don't really understand the 2nd part of your post. It just seems like you are trying to walk down a complex path instead of creating a simple and normalized schema.

Why are you so worried about non sense keys being put in? Will you have user accessing this data in a non standard way?