Tables design for many-to-many relationship

[BethDAT]

I've inherited a database for tracking chemical standard inventory and want
to expand it. Right now, we log all our pure standards (PS) into it. Each
PS may be used to produce one or more working standards (WS). A WS may
contain multiple PSs, and a PS may be used to produce multiple WSs. It looks
to me like the Products/Orders/Orderdetails tables in Northwind is a good
model for setting up tables and forms to track the WSs; is that right?

Each PS and WS has a unique ID. I could use that ID as the primary key, but
the format of this ID is "PS (or WS)-xx-xx" where the x's are numbers that
give first the notebook page and then the line number where the info for the
standard is entered. Will that format cause problems if used as the key -
should I set up the tables with an AutoNumber field and use those as the
PK/FKs?

And one last question. In Northwind it looks to me as if in the
OrderDetails table, the unit price is entered rather than being pulled in
from the Products table. Is that correct, and if so, why was it set up that
way? I would have a similar situation with analogous data for the standards,
so this isn't just an academic question.

Thanks! This forum is a noob's best friend

 

[John W. Vinson]

I've inherited a database for tracking chemical standard inventory and want
to expand it. Right now, we log all our pure standards (PS) into it. Each
PS may be used to produce one or more working standards (WS). A WS may
contain multiple PSs, and a PS may be used to produce multiple WSs. It looks
to me like the Products/Orders/Orderdetails tables in Northwind is a good
model for setting up tables and forms to track the WSs; is that right?

Well... sort of. Northwind has some awkward design issues. It does sound like
you have a many to many relationship that this could be a good design model.

Each PS and WS has a unique ID. I could use that ID as the primary key, but
the format of this ID is "PS (or WS)-xx-xx" where the x's are numbers that
give first the notebook page and then the line number where the info for the
standard is entered. Will that format cause problems if used as the key -
should I set up the tables with an AutoNumber field and use those as the
PK/FKs?

A Primary Key should meet three criteria: it MUST be unique - occuring only
once in the table; it SHOULD be stable - once it's assigned it shouldn't
change (you can use cascade updates if must be, but it's much simpler to not
do so); and ideally it should be reasonably short, for searching and joining
efficiency. It sounds like your PS-32-01 comes close, but it should really
*also* have some identifier for the notebook itself; surely you have many
notebooks, and you might have several with a Page 31, Line 3.

And one last question. In Northwind it looks to me as if in the
OrderDetails table, the unit price is entered rather than being pulled in
from the Products table. Is that correct, and if so, why was it set up that
way? I would have a similar situation with analogous data for the standards,
so this isn't just an academic question.

The Orders application is designed to record business transactions. An
OrderDetails row records the price of an item *as of the time that it was
sold*. If Nehi Orange Soda was sold on December 18, 2001 at $0.75, that's a
historical fact. If the price is now $2.25 you don't want to lose that
historical fact, but you also want to have the $2.25 price available for 2009
sales.

 

[BethDAT]

Thanks for your advice - I'll forge ahead!
BTW, John, the "PS" part is the notebook identifier: when we start a new PS
notebook, its page numbering takes up where the previous one left off, so in
effect we treat them as one huge notebook.

 

[Fred]

I my opinion, the autonumber vs. natural key question will also boil down
whether or not you have the ability to keep changes in the source of the
natural key down to near zero. The PK's are part of the backbone of the
operation of your databases. In my opinion, anything that other people can
mess with should be data rather than / not a part of the PK.