Is it good practice (or would it have any adverse effects) to use a set of 4 columns to identify a row as being unique (one being a foriegn key, the other three being float data types)? I'm attempting to build a table that (with 4 keys linked) would describe a unique entry in the table. I'm curious if this is a good plan of attack or if there is a better way.
For visual purposes, picture the following table. We have inventory items that are organized like the following table: (
[K] is symbolic of the primary key, the lines are relationships)
Sheet_Class Sheet_Type Sheet_Size =========== ========== ========== [K] Sheet_Class-. [K] Sheet_Type--. [K] Sheet_Size '---- Sheet_Class '---- Sheet_Type Length Width Thickness
The data may present itself in the following way, but for brevity I've excluded bringing over the linked columns:
Sheet_Class Sheet_Type Sheet_Size (Tables) [Sheet_Class] [Sheet_Type] [Length], [Width], [Thickness] (Column Values) ============= ============ ============================== Aluminum 5052-H32 48, 96, 0.032 48, 96, 0.040 48, 96, 0.063 6061-T6 60, 120,0.032 60, 120,0.040 60, 120,0.063 Steel 1018-CRS 48, 96, 0.018 48, 96, 0.023 48, 96, 0.031
As it stands (and I've shown in my "schema" above), I use a simple (auto-increment) integer primary key for entries in the Sheet_Size table. However, I'd like to know if it's better to use a combination of the *Sheet_Type*, Length, Width, & Thickness columns instead? Given each entry in Sheet_Size should share all these unique qualities, and that an auto-incrementing field wouldn't demonstrate this well enough, is this the best route to take?
If I'm not explaining the situation well enough, please let me know. I'm finding myself needing to break out these portions (Class vs. Type vs. Actual stock sizes) of an inventoried material for other logic purposes, but I'm up for any other kind of feedback.
Any guidance would be appreciated.
After the answers posted, I've decided to do a combination of Mark's answer & X-Zero's answer. I decided it's a good idea to place a unique constraint on the length, width and thickness columns, but I also like the idea of breaking out material sizes in to unique rows and linking them with a relationship.
Unfortunately I can not accept both answers, so I am going to accept X-Zeros for taking (what I feel) to be a more critical look at the problem and offering a schema adjustment.
Thank you everyone for your answers.