Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2009
    Posts
    5

    Naming convention, Identity and Surrogate

    I am new to this forum so this will be my first posting. I have read a lot and i gather there is two groups, one favouring surrogate keys and another that sees them as a necessary evil. I tend to agree that you should use natural keys as long as possible, only when you don't have one let the database or you yourself generate it programatically. But i have a few questions regarding naming convention and common practise.

    1. Would you say that Id as a suffix is a naming convention for a identity column (auto generated by the database)

    2. Have you ever had a solution where you had you own number series table, where each row represented some type of number series for example order numbers, invoice numbers etc?

    For example:

    Table NumberSeries

    Id (user entered)
    Name
    Type
    Start
    Current
    End

    3. If you had some column used as primary key and you generated it yourself programmatically with a set of business rules, would you still call it a Id?
    For example, if I generate something like this:

    ADD12344GQ = orderNumber or orderId

    and if you would have a new customer
    Company offical name : The Great Company Ltd
    and you either:

    - generated key programatically
    - or entered by use
    would you still call it CustomerId or CustomerNumber?

    What is your experience regarding for example customer id:s? Do you use both a surrogate key and a natural key? I guess you could you things as tax registration number, organization number or some other entity to uniquely identity a customer.

    I have taken customer as an example but i could apply to anything. Supplier, Vendor etc.

    This is just general thoughts and it might be rambling a bit but i would appreciate your input.

  2. #2
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    1. Yes, I always end identity columns in ID, and have seen it several places.
    2. No, I have never done that.
    3. I wouldn't call a character column orderNumber, since it isn't a number. I would either call it orderID or orderKey. I really prefer ID to be computer generated, user entered would keys would end in Key.

    But all of that is my opinion. The most important thing is to pick a standard and stick to it.

  3. #3
    Join Date
    Nov 2009
    Posts
    5
    Thanks Mark, Yeah i guess it as you say. The reason i was wondering is that i have worked for a long time with a legacy system. I haven't really seen a problem with the data model but as i have seen now you solve things differently.
    Another example from the legacy system.

    Customer Order Table

    OrderNumber (PK) generated from a number series table
    OrderType (FK)
    ...
    And other fields

    Customer Order Types
    OrderType (PK) user entered
    Name
    Description

    A solution now days i guess is this one,

    Customer Order Table

    OrderNumber (PK) identity column
    OrderTypeId (FK)
    ...
    And other fields

    Customer Order Types
    OrderTypeId (PK) identity column
    Name (this is the identity you would see as a user)
    Description
    .....

    But lets say you have divisions within a company that are separated from each other. That would mean that you could have a composite key
    with Division and OrderNumber as a key inte the Customer Order Table.

    Customer Order Table

    Division (PK/FK) Here division would be both a foreign key and Primary Key
    OrderNumber (PK) identity column
    OrderTypeId (FK)
    ...

    Does this seem like a odd design?

    I rather like both the number series solution and the legacy way. When i have used identity keys, it became messy generated keys and migrating, and renumbering and such. But maybe it's just me who is inexperienced.

    I actually only see a real need for identity columns, only when you really don't have a natural key like for example for different kinds of logs where the closest to a natural key would be a timestamp. But please give me more examples of when you really need a identity column.

Posting Permissions

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