Results 1 to 8 of 8

Thread: Primary Keys

  1. #1
    Join Date
    Jul 2004
    Posts
    1

    Red face Primary Keys

    Hi All,

    newbie here with a quick question.

    I am designing a database for a game I am making and just noticed something with the tables I am creating.

    I have a table holding all the weapons in the game with:
    - weapon_id
    - weapon_name
    - etc.....

    now weapon_name is always going to be unique. Is there really a need for a weapon_id then when I could use the weapon_name as the primary key? Or is there some preformance thing involved with using an integer as the primary_key as opposed to a string?

    Thanks

  2. #2
    Join Date
    Jul 2004
    Posts
    2
    Uhmm since nobody else is replying I guess I will

    From what I gather (I'm new to this too) there will theoretically be a difference in performance, however it will be so small that it will be unnoticeable in most cases, especially small tables like what I'm assuming you're doing. So sure, you can use weapon_name, but I guess for scalability's/speed's sake you could use a primary integer key.

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    One of the considerations is how often will the key be updated. By implementing a surrogate key, you can avoid cascading updates.
    Last edited by r123456; 07-22-04 at 04:36.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    There are good reasons for using the id, and good reasons for avoiding it. You have to find which makes the best sense for this application.

    Surrogate keys (SKs) are arbitrary. SKs are created for the use of the application, often by the database itself. The user shouldn't ever see or even know that the SK exists, so they should have no desire to change the value of the SK which would cause cascades. The SK should be near optimal in terms of size (typically very small and easy for the application to manipulate), so it should cause much less I/O and database growth when it is used as a Foreign Key (FK).

    The Natural Key (NK, in your example the weapon name) is easier for people to understand. An NK is the "handle" that people expect to use to manage data, so it makes relationships easier to see.

    All told, I'm very much in favor of SKs. They allow the application to run faster, require less storage, and avoid all kinds of complexity. In my book, that's a good thing. There could be cases where the use of the NK would be better, but I've never seen one of those cases!

    -PatP

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    If you do use a surrogate key, be sure to still declare a unique constraint on the natural key; otherwise you will get duplicate rows at some point for sure.

  6. #6
    Join Date
    Jul 2004
    Location
    UK
    Posts
    43
    less storage
    How? Surley it adds to the storage, another column of data.


    I think if the NK is pretty small, and logical to both human view and db view, NK would be better than having another column for an SK. eq Stock codes, cant see any reason why an SK would be needed here.

    With the weapons table an SK might be a good idea because parssing an integer rather than an string is faster, and in game design speed is the essence. If updating the table is frequent, then make sure you have a constraint between the SK & NK.
    Last edited by jwab; 07-29-04 at 11:44.

  7. #7
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Quote Originally Posted by jwab
    How? Surley it adds to the storage, another column of data
    TableA
    --------
    NK(col1, col2) UNIQUE
    SK(ID) PRIMARY KEY

    TableB
    -------
    ID PRIMARY KEY
    taID REFERENCES tableA(ID)
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  8. #8
    Join Date
    Jul 2004
    Location
    UK
    Posts
    43
    Quote Originally Posted by r123456
    TableA
    --------
    NK(col1, col2) UNIQUE
    SK(ID) PRIMARY KEY

    TableB
    -------
    ID PRIMARY KEY
    taID REFERENCES tableA(ID)
    This is because your NK is made from two columns, aka composite. Or am I not understanding this at all? Can you explain further?

Posting Permissions

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