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

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.