Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,127
    Thanks
    27
    Thanked 0 Times in 0 Posts

    Surrogate Key vs. Composite Natural Key

    So I think I have a pretty good design for my Event Registration project, but am still a little unsure of how to tackle the Keys?!

    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:

    1.) For the Order table, should I use...

    - A single Surrogate PK (e.g. OrderID) to simplify working with keys?

    - Or a Composite Natural Key (e.g. CustomerID + ShowID + VenueID + StartDateTime) to more accurately reflect what defines an Order?


    2.) Since this is in MySQL, how will my decision affect "Referential Integrity"?

    3.) Is using a Composite Natural Key going to be a nightmare to code to and maintain?

    4.) Will I have to worry about concurrency issues if I have several people placing Orders at the same time?

    5.) If I choose to use a Composite Natural Key then what do I use for an Order # for the Customer?

    Sincerely,



    Debbie

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,536
    Thanks
    80
    Thanked 4,490 Times in 4,454 Posts
    1.) For the Order table, should I use...
    - A single Surrogate PK (e.g. OrderID) to simplify working with keys?

    Yes, if you think you *may* ever have any other table that will relate to order. You will get better performance

    - Or a Composite Natural Key (e.g. CustomerID + ShowID + VenueID + StartDateTime) to more accurately reflect what defines an Order?

    Even if you make OrderID the PK, you can and should still create a composite UNIQUE key with these fields.


    2.) Since this is in MySQL, how will my decision affect "Referential Integrity"?

    It won't, if you never need to reference the Orders table from some other table. I only suggest using the OrderId as insurance against the possibility you may want to do so in the future.

    3.) Is using a Composite Natural Key going to be a nightmare to code to and maintain?

    I don't see why. It's only purpose is to prevent duplicate records, right? So where is there any maintenance involved?

    4.) Will I have to worry about concurrency issues if I have several people placing Orders at the same time?

    Probably not. But you can always use transactions if you are worried about it. You won't have INSERT issues. The first one to create the composite unique key will "win", even if by a microsecond. Only real problem would come if two people tried to UPDATE the same record at the same time. Seems pretty unlikely, but you can always lock records if need be.

    5.) If I choose to use a Composite Natural Key then what do I use for an Order # for the Customer?

    Another argument for adding the OrderID in the first place, no?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    doubledee (10-29-2011)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,536
    Thanks
    80
    Thanked 4,490 Times in 4,454 Posts
    All of this assumes that one Order will never be for more than one Show. Is that really a good assumption to make?? I can't answer that, of course. You know the situation so you decide.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,127
    Thanks
    27
    Thanked 0 Times in 0 Posts
    I'm surprised by your response. (I guess this is a divided debate?!)


    Quote Originally Posted by Old Pedant View Post
    1.) For the Order table, should I use...
    - A single Surrogate PK (e.g. OrderID) to simplify working with keys?


    Yes, if you think you *may* ever have any other table that will relate to order. You will get better performance
    Well, I've spent the last month thinking things out, and I think I have a pretty solid design...

    Here is my logic...

    An "EVENT" is a physical occurrence and an amalgamation of when a "SHOW" (e.g. Mannheim Steamroller's Christmas Spectacular) comes together with a physical "VENUE" (e.g. Grand Ole Theater) on a particular "START_DATE_TIME" (e.g. Sat, Nov 5 2:00-5:00pm).

    An EVENT is also held in a ROOM, but since that isn't part of the key it can change.

    I guess you could argue that the START_DATE_TIME could change, however, by definition, then this would be a new EVENT?!

    CUSTOMER to EVENT is a many-to-many relationship, with an ORDER serving as the "Junction Table" in between.

    An ORDER has one or more TICKETS.

    I need "CustomerID" + "ShowID" + "VenueID" + "StartDateTime" to describe the EVENT, so I figured that those Natural Keys could/should form the Composite Primary (and FOreign) Key for the ORDER table.



    - Or a Composite Natural Key (e.g. CustomerID + ShowID + VenueID + StartDateTime) to more accurately reflect what defines an Order?


    Even if you make OrderID the PK, you can and should still create a composite UNIQUE key with these fields.
    What is the down-side of doing that?

    Also, even if I create a derived key like "OrderID", I still need CustomerID + ShowID + VenueID + StartDateTime to serve as my Foreign Key to link CUSTOMER and EVENT together, right??


    3.) Is using a Composite Natural Key going to be a nightmare to code to and maintain?

    I don't see why. It's only purpose is to prevent duplicate records, right? So where is there any maintenance involved?
    When a User checks out, this is my Checkout Process currently...

    STEP 1: Choose (an Event) Date and Enter "# of Attendees".

    The page looks like this...

    Code:
    =========================
    Register in 3 Easy Steps...
    
    Step #1: Select a Date and Attendees
    
    Event		Ticket			(Update)	Choose
    Name		Cost	Attendees	Total		One
    Flower Show	$20	___		$0		<<Buy a Ticket>>
    Mankato, MN
    Sept 24, 2011
    
    Flower Show	$20	_3_		$60		<<Buy a Ticket>>
    Willmar, MN
    Oct 1, 2011
    
    Banjo Jamboree	$50	___		$0		<<Buy a Ticket>>
    Brainerd, MN
    Oct 8, 2011
    
    =========================
    So at this stage, we are grabbing the "ShowID" + VenueID" + "StartDateTime" (all keys) along with "NumberOfAttendees" (non-key).


    STEP 2: Log-In or Create an Account.

    Here we capture the "CustomerID"


    STEP 3: Enter Payment Information.

    Just capturing more non-key info.


    Somehow capture the Composite Key Sub-Parts during Checkout, and then somehow "build" my "ORDER" record, right?

    So I'm not sure what my code would have to d to check that there isn't an INSERT issue?!



    Debbie

  • #5
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,127
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    All of this assumes that one Order will never be for more than one Show. Is that really a good assumption to make?? I can't answer that, of course. You know the situation so you decide.
    Yes, you are 100% correct.

    (It would be un-natural - in many ways - to go to TicketMaster and try to buy concert tickets for "The Rolling Stones" and "Lady Gaga" in *one* purchase?!)


    Debbie

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,536
    Thanks
    80
    Thanked 4,490 Times in 4,454 Posts
    Looking at it again...

    Also, even if I create a derived key like "OrderID", I still need CustomerID + ShowID + VenueID + StartDateTime to serve as my Foreign Key to link CUSTOMER and EVENT together, right??
    No, I don't think so. As soon as you wrote "...to link CUSTOMER and EVENT together..." I realized what felt wrong about your design, to me.

    I think I would go with this:
    Code:
    Event
    EventID auto_increment primary key
    ShowID (fk)
    VenueID (fk)
    StartDateTime (fk)
    RoomID
    AvailableSeats
    RegisteredAttendees
    etc.
    INDEX UNIQUE (ShowID, VenueID, StartDateTime)
    
    Order
    OrderID  auto_increment primary key
    CustomerID (fk)
    EventID (fk)
    OrderTotal
    PaymentInfo
    etc.
    
    ... other tables as is...
    The place to force the UNIQUE requirement is on the EVENT description. It is there that you don't want any duplicates of the combination (ShowID, VenueID, StartDateTime). I see no reason to have those three fields appear in the ORDERS table, at all. They describe an EVENT uniquely, and then just a reference to the EVENT is all that is needed in the ORDERS table.

    Also, I don't think you want to make a unique index (which of course includes a primary key) on ORDERS(CustomerID, EventID), at all. After all, a customer *could* purchase 2 tickets to an event and then come back tomorrow to purchase 2 more for a friend. Unless you wanted to go to the hassle of updating the order, you need to allow for that possibility.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    doubledee (10-29-2011)

  • #7
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,127
    Thanks
    27
    Thanked 0 Times in 0 Posts
    I sat down after we last spoke and drew up on a new ERD in my notebook.

    Looks like great minds think alike!

    I'm going to re-hash what you are saying below just to be 110% certain I have all of this down!!

    Here is my new database design...

    First the Table Relationships...

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

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

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

    Venue -||------|<- Room


    Next the Tables...

    Customer
    CustomerID (pk)


    Order
    OrderID (pk)
    CustomerID (fk)
    EventID (fk)


    Ticket
    TicketID (pk)
    OrderID (fk)


    Event
    EventID (pk)
    ShowID (uk)
    VenueID (uk)
    StartDateTime (uk)
    RoomID (fk)


    Show
    ShowID (pk)


    Venue
    VenueID (pk)


    Room
    RoomID (pk)
    VenueID (fk)


    I believe that is what you are describing below?!


    Quote Originally Posted by Old Pedant View Post
    Looking at it again...

    Quote Originally Posted by doubledee
    Also, even if I create a derived key like "OrderID", I still need CustomerID + ShowID + VenueID + StartDateTime to serve as my Foreign Key to link CUSTOMER and EVENT together, right??
    No, I don't think so. As soon as you wrote "...to link CUSTOMER and EVENT together..." I realized what felt wrong about your design, to me.

    I think I would go with this:
    Code:
    Event
    EventID auto_increment primary key
    ShowID (fk)
    VenueID (fk)
    StartDateTime (fk)
    RoomID
    AvailableSeats
    RegisteredAttendees
    etc.
    INDEX UNIQUE (ShowID, VenueID, StartDateTime)
    
    Order
    OrderID  auto_increment primary key
    CustomerID (fk)
    EventID (fk)
    OrderTotal
    PaymentInfo
    etc.
    
    ... other tables as is...
    The place to force the UNIQUE requirement is on the EVENT description. It is there that you don't want any duplicates of the combination (ShowID, VenueID, StartDateTime).
    I guess by "Event Description" you mean the ShowID + VenueID + StartDateTime, right?


    Quote Originally Posted by Old Pedant
    I see no reason to have those three fields appear in the ORDERS table, at all. They describe an EVENT uniquely, and then just a reference to the EVENT is all that is needed in the ORDERS table.
    Similar to the OrderID, I was just afraid that using an "EventID" would lose all of the value that the Event Natural Keys hold.



    Quote Originally Posted by Old Pedant
    Also, I don't think you want to make a unique index (which of course includes a primary key) on ORDERS(CustomerID, EventID), at all. After all, a customer *could* purchase 2 tickets to an event and then come back tomorrow to purchase 2 more for a friend. Unless you wanted to go to the hassle of updating the order, you need to allow for that possibility.
    While it is true that a Customer can only buy Tickets for *one* Event at a time, you are correct in that they could come back and do the same thing the next day. (Missed that one!)

    That is a very good point!!

    Let me know if we are on the same page with what I came up with on my own...

    Thanks,


    Debbie
    Last edited by doubledee; 10-24-2011 at 01:36 AM.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,536
    Thanks
    80
    Thanked 4,490 Times in 4,454 Posts
    I think we are now in violent agreement. <grin/>

    Yes, on all points.

    OrderID isn't necessary, of course, but you might as will have it just so you can have something to print on the customer's receipt. And then, if they have any questions, you ask them for that OrderID and you can find it instantly. Handy, but not necessary.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,127
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    I think we are now in violent agreement. <grin/>
    Ha ha!


    Yes, on all points.
    Glad to hear!


    OrderID isn't necessary, of course, but you might as will have it just so you can have something to print on the customer's receipt. And then, if they have any questions, you ask them for that OrderID and you can find it instantly. Handy, but not necessary.
    What if my client doesn't want the first Customer's Order to be "1"?

    Is there a better way to start at some non-obvious first number (e.g. "12345")?

    (Actually that is pretty contrived too. Any suggestions of how to start off with an Order # that looks like the first Customer is jumping into an existing business?)

    Thanks for the help so far!!


    Debbie

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,536
    Thanks
    80
    Thanked 4,490 Times in 4,454 Posts
    If you are using MySQL, just specify the starting number when you create the table:
    Code:
    CREATE TABLE Orders (
        orderid int auto_increment primary key,
        ... other fields ...
    } ENGINE=INNODB, AUTO_INCREMENT=20188;
    Replace 20188 with your desired starting number.

    Or you can simply insert one dummy record where you *DO* specify the value of orderid. If your value is larger than any orderid value already in the table, then the next record inserted without a specify orderid will automatically use the next number in sequence. And so on.

    That is:
    Code:
    INSERT INTO orders ( orderid, .... ) VALUES( 20187, ... );
    DELETE FROM orders WHERE orderid = 20187;
    INSERT INTO orders ( orderid, ... ) VALUES( NULL, ... );
    SELECT MAX(orderid) FROM orders; /* will return 20188 */
    MySQL always lets you override the auto_increment field's value by passing a non-null value, so long as it is larger than the largest existing auto_increment value.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,536
    Thanks
    80
    Thanked 4,490 Times in 4,454 Posts
    If you are using a different DB, chances are it has a method of doing the same thing.

    For example, with SQL SERVER you do
    Code:
    CREATE TABLE orders (
        orderid INT IDENTITY(20188,1) PRIMARY KEY,
        ...
    );
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #12
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,127
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    If you are using MySQL, just specify the starting number when you create the table:
    Code:
    CREATE TABLE Orders (
        orderid int auto_increment primary key,
        ... other fields ...
    } ENGINE=INNODB, AUTO_INCREMENT=20188;
    Replace 20188 with your desired starting number.

    Or you can simply insert one dummy record where you *DO* specify the value of orderid. If your value is larger than any orderid value already in the table, then the next record inserted without a specify orderid will automatically use the next number in sequence. And so on.

    That is:
    Code:
    INSERT INTO orders ( orderid, .... ) VALUES( 20187, ... );
    DELETE FROM orders WHERE orderid = 20187;
    INSERT INTO orders ( orderid, ... ) VALUES( NULL, ... );
    SELECT MAX(orderid) FROM orders; /* will return 20188 */
    MySQL always lets you override the auto_increment field's value by passing a non-null value, so long as it is larger than the largest existing auto_increment value.
    Okay, thanks for all of your help!!!

    (Probably won't be able to tackle it until this weekend, but I think you helped me over the hump with my database layout!!)


    Debbie


  •  

    Posting Permissions

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