Database relationship design

Enverex

Right, some of my coursework is creating a database and linking the tables acordingly, but I just cant work this one thing out.

The company wants to keep a person or companies records for the future, and some other details, so I make that an entity with the appropriate fields (customer ID, name, dob, phone, etc).

Next they need the order information stored, such as date of the order, progress, date of payment, and customer ID for linking)

But they also want to keep on record, roughly 10 different types of products, so you think, fine, have a table called products, with the fields product ID, name, availability, price, etc. But the point is, how would I link it there?

See, Order is linked from Customer, by putting another Customer ID field in the order table, then giving it a "One customer, to many orders" relationship.

Now what I need to set-up is a "One order to Many Products" relationship.... but what fields could I link there? As I couldn't put a product id field in the order table as they would only allow for one product, and I couldn't put an Order ID in the product table as that would permaently associate that product with that order....

The thing I am thinking of at the moment is making a fourth table which will be unique to each order (thus containing the Order ID) and linking that to both the Order (through the Order ID) and linking it to the table of products through the ID, so that when the ID is entered in the orders product table, the rest will fill itself in.

Does that sound right?

EX

Kwitko

If I follow your last paragraph correctly, I think it states what I was going to suggest. I've come across a similar situation where I had various items that had multiple categories. What I did was create a foreign key table.

In your case, you'd have an Order ID foreign key linked to a Product ID foreign key. Each Order/Product combo essentially makes its own unique ID. The only drawback, now that I think about it, is it only allows for the customer to purchase one of each item. I guess you'd have to add a quantity column.

So you'd have something like:

fk_order_id | fk_product_id | quantity<br />---------------------------------------<br />   100     |   201          |    2<br />   100     |   203          |    1

And so on.

Enverex

Thats what I need. Cheers. Just hope I dont fail this assignment too...

EX

Straight_Man

Your form is a relation expressed as a doc, not a literal table lookup. The old way was to have many parts fields in an invoice table (say order table if it helps), plug in one part number per field into an invoice or order base, and then have the DB do a lookup for part data and plug the query result into the form part by part once the order entry is completely entered, then dump to printer the document built from entered data plus query results. Implicit one-to-many, but really multiple one-to-ones by part number relating to one part, and order needing to allow for multiple parts to be ordered (can substitute products if you wish).

Modern docs are as much query results as they are DB structure, and the more you use queries the simpler your base data set can be. Use field to field relations when possible, one record per order not per item, unless you want each query to gen one order form line. Simplify the one-to-manys when possible and reserve to use for where you have to, like aggregate reports of sales by customer, or "who buys what" queries--invoice gen does not need to be that way. With one to manys, you get into multiple queries in certain orders, or you get utter hash out. Technically, you get into itterative loops which are each multiple or many queries per loop, with every one-to-many you try to manage.

John.

Enverex

I'm basically doing this the way we have been told to do it, basically thats the only way you can do it without failing, as they are not seeing how you achieved the goal (like in my programming assignment) they just want to see the methods they have writen out in the module contract, used.

Below is a pic of it working, and I think that is pretty much what I need. The hard part is explaining how I did it all in about 2000 words. Maybe you could write it for me, eh Ageek?

EX