Electus Matari Forums

My mind has gone blank here for some reason. I can't be sure that my solution to this problem actually works or not, but here goes:

I have a company that sells widgets. There is a table containing the data for each widget type; widgetID (primary key), name, description, price etc.

I have a table for orders, which has the orderID (primary key), order number, a checkbox for if the payment has cleared (they accept cheque), and where the order came from (web, mail order, or phone).

Problem is, customers can order more than one type of widget per order.

What I have implimented is a linking table that has widgetID, salesID (primary key), orderID, quantity.

Does this actually work? In theory, the linking table will hold all of the widgets of all of the orders, like so:


I *think* this works, but I would like a second opinion on it before I start populating the database properly.

Thanks

(Yes, I do get very bored when I don't have decent internet/computer access )

Yes, that works.

It works, but I personally prefer natural primary keys instead of generated ones, so would have the (widgetID, orderID) tuple as the primary key.

Ta very much. I thought it did work, but for whatever reason my brain is asleep atm, and I couldn't be sure

Nice idea with the tuple, but I'll leave it as-is for now I think

One could call your linking table an "order rows" table also. Having separate tables for order headers and rows uncomplicates lots of things.