Sql-server – Guidance for using composite keys to identify rows

schemasql server

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.

Update (08-12-2011)

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.

Best Answer

After thinking about this, I would revise your table structure just slightly.
First, revise your sheet-size table: 

Sheet_size
===========
Id
Length
Width
Thickness

Second, create a sheet-size/type relationship table: 

Sheet_size_type
================
Sheet_Type_Id
Sheet_Size_Id

Then, create the following constraints:

  1. The primary key (and index) of Sheet_size should be the id column
  2. There should be some sort of unique key (and index) enforced on the dimensions in Sheet_size. Consider, are two sheets of dimensions (48, 96, .5) and (96, 48, .5) equal (that is, does the direction of the dimensions matter)? This sort of problem may be difficult to enforce if through the use of the columns as part of the primary key, but becomes more manageable when using constraints and stored procedures.
  3. The primary key (and index) of Sheet_size_type should use both of the foreign keys, starting with the one with the lower cardinality (probably sheet_type, given your example). You may want an additional index in the other direction, but it might not be necessary.

This revision will save you database space (as a ratio of the number of sheet types using the same size), and shouldn't impact overhead too much.

There are other potential concerns about equality/uniqueness if you are using a float datatype, as the imprecision may trip you up unexpectedly. You should consider whether or not a fixed-point type, to some given precision, would be more appropriate.

Related Question