1

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!

Bendos
  • 255
  • 2
  • 11
  • So if a vendor is party, then client is party too? What is the difference between vendor and client in fields (columns)? – Damir Sudarevic Sep 11 '12 at 22:20
  • @damir - have a look at this thread for information on the party model - http://stackoverflow.com/questions/716549/what-are-the-principles-behind-and-benefits-of-the-party-model – Bendos Sep 12 '12 at 19:37
  • I do understand concept of party, but do not understand you application of it. What is the difference between vendor and company? – Damir Sudarevic Sep 13 '12 at 12:51
  • Hi @Damir, there are many companies in our application, only some of which are vendors, hence the extra table (there are many other roles alongside vendor, such as client, club, employer, etc). – Bendos Sep 13 '12 at 21:34

3 Answers3

3

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.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • Personally, I think I'd prefer the temporal tables, over storing the text. Well, until it gets de-normalized for the OLAP database, anyways. – Clockwork-Muse Sep 11 '12 at 23:16
  • @catcall - thanks for taking time to reply. I understand that for accounting purposes you would want an immutable temporal representation of a particular order or transaction and therefore storing the various facts as text would be beneficial. However, are you saying that the order table should have separate _foreign keys_ 'vendor_ID' and 'client_ID' and in addition, text fields to record the vendor and client? – Bendos Sep 12 '12 at 19:44
  • @Bendos - I believe he's stating that, if any fields in `Client` or `Vendor` have the potential to change (and are relevant to the accounting line-item), they would need to be included in `Order`. _Probably_ in addition to the foreign-key columns. Which is why I'd prefer the temporal tables... – Clockwork-Muse Sep 12 '12 at 20:16
  • @Bendos: You need to store enough information to be able to reproduce your accounting paperwork whenever a government official, a judge, or an arbitrator requires you to. You can meet that requirement in several ways, but in general you need to store somehow whatever appears on that paperwork. That *might* mean just storing a jpg image. Personally, I'd try to buy commercial, off-the-shelf accounting software rather than writing something like this. – Mike Sherrill 'Cat Recall' Sep 12 '12 at 20:18
  • Thanks guys. @Catcall - I do understand the importance of accounting paperwork and, as you say, even a print-out might suffice. However, my question is an honest one - I'd like to know how to approach the problem in a broad way. Forget accounting requirements for a moment - our system also has a membership system which logs 'entries' to a club. I have the same problem there of how the 'entries' table references the 'club' and 'member' tables (where club and member are subtypes of party). Thanks for your input! – Bendos Sep 12 '12 at 20:33
0

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.

Clockwork-Muse
  • 12,806
  • 6
  • 31
  • 45
  • Thanks @X-Zero. This was kind of where we were headed. If we use something like `occuredAt`, then as you say orders can only be placed once a millisecond (probably not a problem?) however, this smells of an inadequate solution. My suggestion of `order_ID` is basically the same, but if it were made to be auto-increment, then there is no issue with orders happening at the same time. – Bendos Sep 12 '12 at 20:04
  • Well, it's _probably_ an adequate solution, and will be for most circumstances. If all sales for a particular client/vendor are batched up per-day, it's not going to matter, for example. I'm not aware of any situation where simply delaying an order for a millisecond or two would be a large problem. For something like branch offices ordering from the same supplier (everybody runs out of TP on Monday?), all the branch offices should probably have their own `id_client`, probably. But it's a wide key, hence a completely surrogate key. – Clockwork-Muse Sep 12 '12 at 20:22
0

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.

enter image description here

Damir Sudarevic
  • 21,891
  • 3
  • 47
  • 71