Post reply

DB structure question

  • March 5, 2004 at 4:56 pm

    #84712

    On table structure, is there an advantage to having two or more columns making up the PK vs a new identity column for the PK?

    Also, if I have a one to one relationship between two tables (say one table is a subset of the first), should I have a new identity column in the smaller table PK?

    Let me know if my questions are clear or you need more information.

    Thanks for the help!

    SMK

  • March 5, 2004 at 5:24 pm

    #497685

    It seems to me the following are issues in determing the answer to your question.

    I call using two or more columns to make up a primary key a "composite key".

    First, a primary key may not contain a nullable column.

    Second, an "identity" column has some contraints in how it is used (replication, ?) and maintained (you will not always get a strict incrementing sequence, if that's desired).

    Primary keys tell you something about the row data, where as identity numbers don't tell you anything about the data.

    You have not mentioned whether or not each row in the table is uniquely identified.  For the purpose of this explanation, I will assume they are unique.

    Given the info provided, my thoughts are as follows:

    Create a unique composite key in each table.

    The key in table two could also be a foreign key to table one.  Take advantage of referential integrity feature of SQL2K.

    However, one caveat for identity keys.  If the composite keys were very long, this of course would drive your disk space requirements for the DB up, where as identity keys would diminish this requirements.  You have to make the call.

    Will look forward to seeing others comments on this topic.

    GaryA

     

     

     

     

     

     

     

     

     

     

  • March 5, 2004 at 5:59 pm

    #497687

    In this DB, almost all PKs are unique identity columns which you are right, tell you nothing about the data in that row. It doesn't matter if there is strict incrementing, I just need a unique identifier.

    In tables in which a composite key would be appropriate, such as "associative" tables or whatever you guys call the many-to-many connectors, I have generally made a new identity column and then the two columns connecting the tables: ClientPhoneID, ClientID, PhoneID

    I don't do this for any reason in particular other than it just seemed like the right thing to do.

    Replication is not currently an issue, but likely will become an issue within the year.

    Is there any reason not to have a relationship on ClientID table1 to ClientID table2 when both of these are Pks and it's one to one?

    Thanks for the help. I am just trying to be consistent across the DB and need to do it one way or the other every time.

  • March 5, 2004 at 9:31 pm

    #497693

    Additional thoughts are:

    Since there is a one to one relationship between tables, it seems to me that to use an identity column (for a key) implies that the same identity value is needed in both tables (for the same row).

    I suspect that over time there may be difficultly in keeping the identity values in sync.  i.e.  An insert on table 1 fails, but identity value is still incremented.  Problem corrected, insert now succeeds.  Now when you attempt to insert to table 2, it succeeds on first attempt, but you don't get the same identity value.

    However the above problem could probably be avoided through the use of "transaction" boundaries or the use of stored procedures.

    Where as using column data (composite keys) eliminates the above difficulty.

    HTHs

    GaryA

     

     

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply