www.webdeveloper.com
Results 1 to 11 of 11

Thread: Database design tip?

  1. #1
    Join Date
    Jul 2006
    Posts
    22

    Database design tip?

    Hello,

    Im designing a database and find that I seem to have a few small tables with say, 3 or 4 fields, where all of those fields are required to identify a record. Is it better to make a composite primary key consisting of all the fields, or introduce a non-composite primary key, like an auto_increment integer?

    Thanks for your help!
    Keith

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    18,918
    Generally, I like to have an auto-increment integer field as the primary key, mainly to simplify relations between other tables. If it is a logical requirement that a combination of the other data fields must be unique, then you can still assign a composite unique index on those fields. (Hope that helps -- and hope I understood your question. )
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Jul 2006
    Posts
    7
    Proper design technique is to have a primary key set on columns that would make your result unique. You call it "composite unique index".
    Autoincrement is not a good design practice.

  4. #4
    Join Date
    Feb 2003
    Posts
    2,745
    Quote Originally Posted by CCCP
    Proper design technique is to have a primary key set on columns that would make your result unique. You call it "composite unique index".
    Autoincrement is not a good design practice.
    Not necessarily true. suppose u need foreign keys to point at such a composite key. this is exactly proper use of auto-incremement type fields. also, simplifies client code.

    it is true though that the natural, or "business primary key" should be defined by business rules and reflected in the db, there are certainly valid uses for identity (auto-incrementing) fields.

  5. #5
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    379
    A foreign key would point to only one of the fields in the composite key. I don't see where the problem is russel. Can you clarify?

    From my understanding, an auto increment has no real meaning to it. If you reference a foreign key to an auto increment key, and the value of that key is 4, 4 does not tell you much.

    But if it was referencing something meaningful, such as C10001, then it make more sense that that's the customer number (bad example, but i think you know what i mean). In my oracle class we're told to stay away from them, so I would love to get more insight on this subject.
    The beauty of code is in its simplicity.

  6. #6
    Join Date
    Jun 2006
    Posts
    472
    Database design and teaching must have changed since I went to Uni if they are telling you to stay away from auto-increment primary keys. If you create a database application that lets the users enter in customer information, would you expect the user to know what id to use for a customer they enter into the DB? If they have thousands of customers how would they know a unique number to identify the next customer? As Russell said "business primary keys" need to be apart of the business rules. I certainly use auto-increment in my main entity tables and a composite primary key for my associate tables.

    JayM in Oracle you create a sequence and enter the next.sequence number into the DB, that is the same as auto-increment, unless it has changed since oracle 9i
    Last edited by aussie girl; 11-24-2006 at 11:09 PM.

  7. #7
    Join Date
    Nov 2006
    Posts
    16
    Quote Originally Posted by JayM
    A foreign key would point to only one of the fields in the composite key. I don't see where the problem is russel. Can you clarify?
    This isn't necessarily true: if logical primary key is composite, then none of individual fields are unique. That most likely means any table referencing this key will have to contain same set of fields.

    Quote Originally Posted by JayM
    From my understanding, an auto increment has no real meaning to it. If you reference a foreign key to an auto increment key, and the value of that key is 4, 4 does not tell you much.
    It doesn't need to tell you anything. You know the relationship, you can select actual data...

    Quote Originally Posted by JayM
    But if it was referencing something meaningful, such as C10001, then it make more sense that that's the customer number (bad example, but i think you know what i mean). In my oracle class we're told to stay away from them, so I would love to get more insight on this subject.
    Err.. Whomever taught that class needs to be taken out and shot, at least if
    your relayed this info correctly.
    Yes, when you design your logical, fully normalized, structure, you need to understand what actual defining unique data is, but when you get down to actual schema, you need to make it actually work. This, among other things, means you really don't want to have multi-column joins, and things like that.

  8. #8
    Join Date
    Feb 2003
    Posts
    2,745
    Quote Originally Posted by JayM
    From my understanding, an auto increment has no real meaning to it. If you reference a foreign key to an auto increment key, and the value of that key is 4, 4 does not tell you much.
    this is what joins are for. suppose your table has

    id name
    1 JayM
    2 russell

    u are right that 1 means nothing, but we can select name by joining on id.

    there are other valid uses of auto-incrementing fields as well. how about purchase order numbers, sales orders, customer IDs...

  9. #9
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    379
    Makes sense. But customer order numbes, cust ids and those other fields should be generated through some sort of script. I would not expect a customer to enter something like that. If you look at large websites, no site has cust id 1, or cust id 99. Hence they have some sort of script generating a unique number, and inserting it as a unique key in the cust id table.

    Iluxa, it's not about whether your not YOU know the relationship, it's about the next maintenance programmer thats going to come after you. Also, C10001 would work pefectly fine in a schema, so I don't see a problem there.

    Great discussion guys/girls. Sorry for the original poster, I sort of hijacked this uknowingly.

    I just think that there's a better way to design a table than a auto-number/next sequence. It might be fine initially, but perhaps it would complicate maintenance down the line. I mean, if you can reference real data, why create an extra field that is somewhat meaningless? Perhaps different rules apply to the business world?
    Last edited by JayM; 11-26-2006 at 10:21 AM.
    The beauty of code is in its simplicity.

  10. #10
    Join Date
    Nov 2006
    Posts
    16
    Quote Originally Posted by JayM
    Makes sense. But customer order numbes, cust ids and those other fields should be generated through some sort of script. I would not expect a customer to enter something like that. If you look at large websites, no site has cust id 1, or cust id 99. Hence they have some sort of script generating a unique number, and inserting it as a unique key in the cust id table.
    Well
    A. Some sites do have purely numeric order IDs. They might prepend 0's to
    them to make entering them harder, but still
    B. If you want customer to enter alphanumeric string that makes as little sense
    as numeric does, but is harder to type, since it's all over keyboard, instead of just
    numeric pad, nothing prevents you from generating them, and storing alongside
    with actual ID.

    Quote Originally Posted by JayM
    Iluxa, it's not about whether your not YOU know the relationship, it's about the next maintenance programmer thats going to come after you. Also, C10001 would work pefectly fine in a schema, so I don't see a problem there.
    Next maintenance programmer will inherit your database ERD, that you already
    drew and that is hanging on the wall of your cubicle/office/whatever, ant that
    is stored in your SCM system, right?
    He should be able to figure things out from there...


    Quote Originally Posted by JayM
    I just think that there's a better way to design a table than a auto-number/next sequence. It might be fine initially, but perhaps it would complicate maintenance down the line. I mean, if you can reference real data, why create an extra field that is somewhat meaningless? Perhaps different rules apply to the business world?
    One word: performance.
    Also logic, of course. If you try to encode extra relationship info into data
    (i.e. by saying "All customer IDs should start with 'C'") and then your program
    will rely on this somehow, database engine will not know, and will not be able
    to enforce these constraints. Neither will your next maintenance programmer
    know about it until he breaks the rule, and spends two days looking for the code
    that was expecting your data to be in some specific format.

  11. #11
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    379
    Ahh, I understand. I guess it does have it's advantages afterall. Thanks for the clarification.
    The beauty of code is in its simplicity.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles