Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
golden rule
Starting Member
4 Posts |
 Posted - 2008-05-02 : 17:43:53
|
| Hi,I'm neck-deep in trying to normalize all the info I need into separate tables, but I seem to have hit a wall running.I was curious as to what the "best" way is to develop a database of concert events would be? What I think I've figured so far would be if I created a table called events...that would be 1 row of data per concert event. Each row could then have a 'event_id' as PK, 'date', and a column with the associated 'band_id' from the bands table. Then anytime the date was accessed (assuming a populated row for that date), a list of concerts for that date would show.I would also like to use the 'event_id' as an access point for my reviews table...where, quite obviously, I would like to be able to have reviews associated with each event from the calendar as well. Does all this seem right? Or is there a better way (i.e. more efficient, smaller database, and/or use less memory)? How, for example (in short, obviously), does a website like Pollstar.com database their events?I guess my question with regards to this database is also...within my bands table, should I include their tour dates? Or should I put the band_id in my events table?I was hoping to get a just a little bit of direction so I don't turn some baby missteps into a long-term project down the wrong road. Any help or response is greatly appreciated. Thanks again! |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-05-02 : 18:06:18
|
| It is not a good idea to have the band_id in the events table. You could have more than one band at an event, so you need an event_band table with FK event_id and FK band_id columns to correctly model the many-to-many relationship.You will probably need a similar design for the review table. An event can have more than one review, and a review could cover more than one event, so you need an event_review table.In general, when there is a many-to-many relationship between two entities, you will need to have a table to hold that relationship:Table Entity1 with PK Entity1_id,Table Entity2 with PK Entity2_id,Table Entity1_Entity2 containing FK Entity1_id and FK Entity2_idCODO ERGO SUM |
 |
|
|
golden rule
Starting Member
4 Posts |
Posted - 2008-05-02 : 19:34:54
|
| Awesome. That all makes good sense. I like the idea of accounting for multiple bands and can definitely grasp the concept.Just one step further to see if I understand...See the embarrassingly poor table below. Assume it is my EVENT_BAND table. How can I then account for if there are 4 band_id's I need to include? And if I make a table that allows up to 100 bands, isn't that just a waste of memory? I'm confident this is just my novice shining bright, but can't I include a number of bands in one column?+---------------+---------------+-----------------+--------------+--------------+--------------+| date | event_id | venue_id | band_id | band2_id | band3_id |+---------------+---------------+-----------------+--------------+--------------+--------------+| 01-01-2009 | 1 | 32 | 5 | 7 | 12 || 01-02-2009 | 2 | 11 | 16 | 3 | 19 |+---------------+---------------+-----------------+--------------+--------------+--------------+ |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-05-03 : 01:28:43
|
It is not a good design to have multiple columns for band_id in the EVENT_BAND table. Instead, you would just insert another row in the table for each event/band/venue/date combination.The four columns look like the combination is a candidate key, meaning that they are a unique combination that should only occur once in the table. In that case, EVENT_BAND_VENUE_DATE would probably be a better name for the table.You should spend some time reading about normalized database design before you actually design your database. A normalized database makes it much easier to develop your application and maintain correct data. A non-normal design makes it very difficult to do this, makes it take longer to develop the system, and is very costly to correct later.For example, if you had your band_id’s in a hundred columns in EVENT_BAND, it is very difficult to write a query to return a simple list of bands that were at an event. With the design I suggested, it would just be:select band_id from EVENT_BAND_VENUE_DATE where event_id = 3 CODO ERGO SUM |
|
|
|
golden rule
Starting Member
4 Posts |
Posted - 2008-05-03 : 04:51:19
|
| Perfect. Thank you for the info. You actually provided me with the answer I was looking for. And I will definitely soak up some more on the normalization process. I am reading a book right now by Peachpit Press and I think I need to get a "clearer" understanding as you mentioned. Thanks again for the help! You definitely put me in the right direction. |
|
|
|
|
|
|
|
|
Concert calendar/reviews database development?