How should this database sub type relationship be modelled?

Question

I am revising a legacy multi-tenant application where the shopping cart function stores multiple vendors and multiple clients in the same database. Some clients of one vendor may be clients of a different vendor. Some vendors might actually be clients of another vendor.

I currently have a table for the super-type 'party' with primary key party_ID, a table for the subtype 'company' with primary key company_ID (references party_ID) and a table for the role of 'vendor' with primary key vendor_ID (references company_ID). I also have a junction table, 'client' with a composite primary key of vendor_ID and party_ID.

My question is how should the 'order' table reference the vendor and client tables? My first thought is that the table should have a composite primary key of vendor_ID, client_ID and order_ID (order_ID could be auto-increment across the table or sequential per vendor_ID + client_ID) but this seemed a bit fishy as there were three attributes making up the key...

Does anyone have any insight into this topic? Most 'shopping carts' only deal with a single vendor, so the order table simply lists client_ID as a foreign key.

Thanks!

Answer 1

I would start with something like this. I do admit that I still do not quite understand difference between company, vendor, and client in your question. As Catcall mentioned, in this model you are not allowed to delete Parties (People, Organizations); accounting records should be frozen -- usually by capturing current customer/supplier info in order table.

---

Answer 2

My question is how should the order table reference the vendor and client tables? My first thought is that the table should have a composite primary key of 'vendor_ID', 'client_ID' and 'order_ID' but this seemed a bit fishy as there were three keys...

Composite primary key doesn't mean three keys. It means one key consisting of three columns.

But that's not the real issue.

An order is an accounting record; it must not change over time. Storing the ID numbers is risky unless you've built temporal tables, and I doubt you've done that. If a vendor changes its name today, its name no longer matches the name on earlier orders. You must not let that happen with accounting records.

Unless you mean something unusual by "order", I'd expect Order_id to be its primary key. There might be other constraints; there might even be other key constraints to prevent duplicate orders that differ only by Order_id. But I'd still expect Order_id to be the primary key of a table of orders.

If vendors and clients are subtypes, I'd expect any (high risk) id numbers you store to reference the id numbers in the subtype tables. In your case, you seem to have an additional table that identifies the clients of vendors; it contains the columns {vendor_id, client_id}. The foreign key references for that table should be obvious.

Your table of orders should have one foreign key reference to that table, not one foreign key to vendors and another foreign key to clients. So in the table of orders, foreign key (vendor_id, client_id) references vendor_clients (vendor_id, client_id). The table of vendor clients will need either a primary key constraint or a unique constraint on {vendor_id, client_id}.

But you shouldn't do that for accounting unless you're using temporal tables. Instead, you should probably store both the id numbers and the text.

Answer 3

For your primary key, you'll want just order_id.

Really, the composite (and unique) key I would use would be [vendor_id, client_id, occurredAt] (where occurredAt is a timestamp) - assuming orders could only be placed once a millisecond. However, this is something of a wide key, and some systems don't appreciate those. You'll still need these columns, and probably indexed, however.