Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2011
    Location
    Arizona
    Posts
    24

    Artificial Key vs. Composite Natural Key??

    I could use some help with Keys in my database design...


    Here are my Table Relationships...

    Customer -||-----|<-Order->0-----||-Event

    Order -||-----|<-Ticket

    Event->0------||-Show
    Event->|------||-Venue
    Event->0------||-Room

    Venue-||----->|-Room


    Here is my Table layout...

    Customer
    CustomerID (pk)
    Name
    etc.


    Order
    OrderID (pk??)
    vs.
    CustomerID (pk)(fk)
    ShowID (pk)(fk)
    VenueID (pk)(fk)
    StartDateTime (pk)(fk)
    OrderTotal
    PaymentInfo
    etc.


    Event
    ShowID (pk)(fk)
    VenueID (pk)(fk)
    StartDateTime (pk)(fk)
    RoomID
    AvailableSeats
    RegisteredAttendees
    etc.

    Show
    ShowID (pk)
    ShowName
    ShowSynopsis
    etc.


    Venue
    VenueID (pk)
    VenueName
    Address
    etc.


    Room
    RoomID (pk)
    VenueID (fk)
    RoomName
    Capacity
    etc.


    Ticket
    *pk depends on Order
    TicketPrice


    QUESTIONS:

    For the Order table, is it better use just one Artificial PK (e.g. OrderID) or have a Composite Natural Key (e.g. CustomerID + ShowID + VenueID + StartDateTime)?

    I suppose the Artificial Key is easier to work with, however it is just an arbitrary number.

    Using a Composite Natural Key is, well, more "natural" (and self-identifying), but it also makes linking tables together much more difficult.

    And if I am doing this in MySQL, I'm not sure how this affects "Referential Integrity"?! (Maybe I'll be forced to write a lot of fancy Application Code to enforce things?)

    There is also the question of what do I use to create an "Order #" for my Customers if I use the Composite Natural Key?

    Lastly, one "Order" can have one or more "Tickets", so I have to factor in that these tables are also linked when making a PK/FK decision.

    What do the "Database Gods" recommend, and why?

    Thanks,



    Debbie
    Last edited by doubledee; 10-23-11 at 17:42.

  2. #2
    Join Date
    May 2008
    Posts
    277
    Whether or not to use an artificial key or a natural key is a very contentious, largely philosophical debate. However, you must ALWAYS enforce the natural key (using a UNIQUE constraint) even if you decide to use a surrogate key as your primary key. I tend to use surrogate keys for tables that hold user-entered data. Also, if a table has a non-transferable relationship with another table (i.e., the table represents a weak entity), I will include the foreign key to the other table in the primary key, resulting in a "composite surrogate key".

    For example, as it stands, your Room table does not appear to prevent someone from entering an infinite number of "room 1"s for a venue. You must enforce the natural key, which would be a composite key consisting of the venue and room name/number. If you choose to keep RoomId as a surrogate key, I would also include VenueID in the primary key, since we cannot have a room without a venue, and we also wouldn't expect a room to move from venue to venue. So I'd end up with two keys on the table: a primary key consisting of VenueId and Roomid, and a UNIQUE NOT NULL constraint consisting of VenueId and RoomName.

    Your Event table also has problems. It is essentially an intersection table between a Show and a Room (not a Venue, which can be derived based on the room) and a time period. However, there are several other keys here as well, one which consists of a Room and a time period, since we cannot double-book a room, and another which consists of a Show and a time period, since presumably the performers cannot be in multiple places at once. AvailableSeats and RegisteredAttendees are not needed, since they can be derived from the room capacity and number of sold tickets.

    It's debatable if you need a separate ticket table; you can simply add a column to the Order table indicating the number of tickets sold.

Posting Permissions

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