8

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.

4 Answers 4

6

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.

3
  • I was planning to limit the Length and Width to a single (possible two) decimal points, and thickness would (at most) extend out to three. Beyond that, we're getting too finite (and the stock itself never comes in at the describes numbers anyways). That aside, I like the thought of breaking out the sheet sizes, but the problem I'm facing are the other columns involved (that I've excluded). (need more room, see next post) Aug 11, 2011 at 15:07
  • Because this is a list of inventoried stock, I have to include other information such as Density and Cost/lb (which is heavily based on the type (and thickness even. For example, "Steel"/"1018" could be $0.55/lb at 0.018-0.125" thick, but becomes $0.65/lb once the thickness goes beyond 0.125". (And this also can differ between a 48"x96"x0.250" sheet size of 1018 versus 5052-H32). In your example, I'd only have one entry for a 48"x96"x0.125" (though I suppose the relationship table could have these additional metrics) Aug 11, 2011 at 15:10
  • If you only need a small number of decimal places, then yes, use a fixed precision. Yes, that's where (in this case) you'd put information like that (cost is a dependency on the sheet type and size, for example), although you may want to generate additional tables that can be referenced. You may also want to consider making custom data-types (like density) so people don't try to do query your data in unexpected ways. Aug 11, 2011 at 15:46
6

Looks like a natural vs surrogate key decision, opinion on which ranges from considered and practical to academic, bordering on dogma. Depending on the RDBMS, there are considerations for the physical model which can have significant performance implications e.g. clustered key choice in SQL Server.

Personally, if I have a narrow, single attribute candidate key, I’m tempted to make use of it. Wide and/or composite keys, by default I’m adding a surrogate to the model. In your case, I’d vote for the identity column on Sheet_Size as primary clustered key and a unique constraint on type/length/width/thickness.

3
  • But given that you now have an "arbitrary" key given to the row, how does unique enforce that the columns (when combined) can not have duplicate values? It's my understanding that the unique attribute relates to the key. You're saying Sheet_Size INT PRIMARY KEY and Length UNIQUE, Width UNIQUE, Thickness UNIQUE? I still don't understand how that prevents duplicates in the table (without applying logic to the insertion interface). (Maybe I'm missing something?) Aug 11, 2011 at 15:01
  • A unique constraint on the three columns: ALTER TABLE dbo.Sheet_Size ADD CONSTRAINT UC_LengthWidthThickness UNIQUE ([Length], [Width], [Thickness]) Aug 11, 2011 at 15:14
  • Thank you for the feedback. I do agree a unique constraint on the columns would be a great solution, but I also like X-Zero's recommendation of breaking the sizes out in to a new table (linked with a new table). So to combine ideas, I will apply the unique constraint tot he "Stripped-down" size table, while removing the density & cost/lb information and placing it in the relationship table. Aug 12, 2011 at 18:32
4

I'll redirect you a bit to this answer from a previous question.

Quote: " Regarding the way of designing that primary key, there are two schools of thought:

  • one that makes the PK as a separate column, usually self generated, like a GUID or and auto increment INT (in your case a separate uniqueidentifier column);
  • one that makes the PK as a column (or set of columns) internal to the table (in your case would be a username or email or SSN, whatever makes that user unique) that uniquely identify a record.

To what line you adhere, it's only a matter of taste."

Side effects of any chosen solution could be:

  • using compound keys everywhere will likely:

    • increase storage for all involved tables;
    • increase/complicate indexes on often used FKs;
    • complicate a bit the writing of all your join statements
    • make Mr. Joe Celko happy :-) (references about his opinions regarding natural or artificial keys can be found here and here, and mostly everywhere he's asked about the issue)
  • using generated keys will likely:

    • simplify the previous 3 steps
    • complicate the situation of replicating a table with identity PK (references here, here or here)

I personally favor generated INT IDENTITY keys, but what suits you should be fine.

2

The composite key makes perfect sense. Implementing that key ensures that the business attributes can't be duplicated. That's a good thing because recording the same data multiple times would cause ambiguity, undesirable dependencies and make user errors and incorrect data more likely.

The auto-incrementing key alone won't protect the integrity of your business data. If the auto-incrementing key serves no particular purposes (e.g. as the target of a foreign key reference in another table) then it can safely be dropped.

2
  • ... Except dropping the auto-increment as the foreign key would require using all of the dimension columns as part of the foreign key (that is, all four columns, when including type). Not something I want as my foreign key, period - single columns only, please. I agree that it's a good idea to put a unique key (and/or check constraint) on the dimensions (and type, depending on table design). Aug 11, 2011 at 16:10
  • @X-Zero, I did make the point about foreign key references in my second para. The question as I read it is whether to implement the composite key, not whether also to have an auto-increment.
    – nvogel
    Aug 11, 2011 at 16:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.