Database design: is there a better way than a 3-field composite primary key?

I am trying to model a database for a nightclub with several locations with several venues. I created 3 tables in my database

Venues
Bands
VenueSchedule

I figured that VenueSchedule would be the join table between these, and the primary key would be venue_id and band_id, so that you could look up which band has played in which venues. But the wrinkle is I also need to be able to query when the bands pla开发者_运维技巧yed on that venue, and which bands will play on a given venue on a particular date.

VenueSchedule table's primary key has 2 fields now. Would it be bad practice if I add date field into the primary key? Is there a better way than this?

---

You know your data better than I do, but it seems to me that (venue_id, band_id, date) would be the most likely key for VenueSchedule. (venue_id, band_id) would imply that each band could only play each venue once or that the date wasn't needed.

I would advise you to be cautious about the use of a surrogate key as suggested by other replies here. Firstly, a surrogate key won't do the same job as the compound key: it won't maintain data integrity because a surrogate won't stop the same venue, band, date combination being inserted more than once. That would create potential update anomalies and could lead to inaccurate results.

Secondly, on the information given it isn't obvious why you would even need a surrogate key or how it would be used. If the table is being referenced elsewhere then you might want to use a surrogate for that - but if you have no use for it then a surrogate key would just be extra weight and complexity in storage and indexes for no good purpose at all.

---

I would not do this. I would use a surrogate key (like an Identity in SQL Server). A surrogate will be faster to retrieve on, and your FK tables will be more efficient and smaller because you won't need to add these three columns to each FK table. In most cases, I'm a big fan of using surrogates, although, database purists would strongly disagree because a surrogate has no relationship to the other columns in the table, IOW, it's an artifical column used strictly for a PK. I understand this position but I use them and recommend them anyway.

---

Although big fan of surrogate keys (Id), I tend to avoid them in "dependent entities", because it is quite easy to duplicate data. If I were to add an ID to the Schedule table, a unique constraint would be needed on (VenueId, BandId, Date, Status) which adds another index to the DB.

I have added the Status field to allow for canceled performances.

---

I'd have a surrogate primary key and unique constraints instead.

---

Sounds more like an issue of getting at the data, rather than storage. Setting up a view to give you the data you need, or using a data warehouse to store the data in a denormalized fashion is an option for reporting needs.

For storage, like was mentioned, I would recommend using a table that has an artificial primary key and this table points to these other three references (venues, bands, and venueschedules), so one table ties the three together, and this way, you can write a query using this one that links to the other three to get at the data.