How would you design this relational database?

I've been given lot's of ideas that I had no idea how they would translate into real life, I've just designed m开发者_开发技巧y first one to many table schema so please bear with me :)

I have an events table that contains information such as music genres. What I did is that I created a separate table called music_styles. Each row in music_styles contains an ID and a name for that genre. Until now, I took that ID and put inserted that into a foreign key column in the events table, which works if each event were to have only one musical genre. So what I wanted was a solution for multiple genres and I was told the way to go was to create a third table that would reference both an event id and a music_style id. How would that work? I have no idea how to start so any thoughts are welcome. Thanks!

You have to create a third table, we can call it events_music_styles with two columns: event_id and music_style_id; event_id will obviously contain the id of an event and music_style_id the id of a genre.

This way you can have for example two or more rows with the same event associated to different genres. You should also remove the information about music genres in the events table.

This is the standard way of doing this. You should study a little bit of database design and normalization, there are important but easy concept to learn that will really help you.

Your main table is Events and each event will have one entry with and id (maybe a GUID to make life easy). You will have a second table which will then have one or multiple rows which will have the music styles for the event. The music styles will be referenced off of the third table (which you already have) for genres.

A good design will have any item be it event or genres only in one place and referenced as many times as you need.

What you are referring to is called a many-to-many relationship or lookup. It works because any combination of entities from either table can be explicily linked together through the third table.

For example:

Table 1 - music styles
1 Techno
2 Breaks

Table 2 - music event
1 Blagfest
2 Rockers Night

Let's say that the Blagfest event has techno and breaks and that Rockers Night has just breaks. The third table would contain the following pairings:

Event Style
1     1      - Blagfest has Techno
1     2      - Blagfest has Breaks
2     2      - Rockers night has Breaks

Hope that helps.

pufAmuf,

You'd create a third table, say event_styles, with only two columns: event_id and music_style_id. If an event referenced only one style, you'd add one row to the event_styles table with the appropriate event_id and music_style_id. If an event referenced several styles, you'd add a row for each style. Each of those rows would contain the same event_id and the appropriate music_style_id.

For example, suppose event 01 references pop, and event 02 references jazz and rock:

|events                 |  | music_styles |  | event_styles               |
|-----------------------|  |--------------|  |----------------------------|
| id | data             |  | id | style   |  | event_id | music_style_ id |
|----+------------------|  |----+---------|  |----------+-----------------|
| 01 | "event info"     |  | 01 | jazz    |  | 01       | 03              |
| 02 | "2nd event info" |  | 02 | rock    |  | 02       | 01              |
-------------------------  | 03 | pop     |  | 02       | 02              |
                           ----------------  ------------------------------

What you said is correct, EventID and StyleID in TBL_EVENT_MUSIC_STYLE will point to EventID and StyleID of TBL_EVENT and TBL_MUSIC_STYLE respectively.

TBL_EVENT(EventID,Column1,Column2)
TBL_MUSIC_STYLE(StyleID,Column4,Column5)
TBL_EVENT_MUSIC_STYLE(EventID, StyleID)

generally you don’t use plural names for table names. So it’s better if you can have table names as music_style etc rather than music _styles

Sriwantha Sri Aravinda