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 |
|
ams001
Starting Member
3 Posts |
 Posted - 2008-10-25 : 13:52:40
|
| Hi I am on a web based event guide for my city, I have a question about designing the tables.For simplicity my structure will look like this:table event:event_nameevent_typeevent_dateevent_locationtable places:Place_idPlace_namePlace_addresstable rooms:Room_idRoom_NameRoom_Place ---> Places[Place_id]An event has to occur in a place, sometimes the place might have more than one room so i need to link the event to a specific room. How can I acheive this? Do I create a dfault room to every single place and link the events to rooms rather than places? or should i add another field to the event with room_id and use it in case the place has a subroom. The first solution looks stupidly redundant while the second solution is not very consistent!!ThanksAhmed |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-25 : 14:05:39
|
| i think you need both placeid and roomid columns in table_event with FK relation to source tables. Can there be a chance that an event takes place at more than 1 place? |
 |
|
|
ams001
Starting Member
3 Posts |
Posted - 2008-10-25 : 19:19:11
|
| 1) If I put a roomid column in table_event what do i do with places that have no subrooms? won't the roomid be required for every single event in this case?2) To answer your good questions yes and no :) i was thinking of only listing single place events. Multiplace events can be something like a festival maybe but then they will probably be composed of several multi single place events. Thanks for pointing this out, maybe I should add some sort of association field in the event table to use it in case i would like to query events that are somehow related.3) I also have the problem of multiday events, like an exhibition. I was planning for creating multiple single day events for these but also looks too redundant to me, any ideas on that as well?Thanks for your help |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-26 : 01:30:16
|
| what about keeping a seperate table say event_details with event_name,placeid,roomid. the roomid can be a nullable field which will be null when you dont have any subrooms.. Also multiple place events will have more than 1 record in table with different placeids. |
|
|
|
ams001
Starting Member
3 Posts |
Posted - 2008-10-28 : 07:34:03
|
| Thanks visakh16.I think I will add a column room_id to the event table like you suggested. I will depend on the front end to lets say populate a drop down list of rooms when certain places are selected. Even if something goes wrong and an incorrect room is inserted in an event, it will be easy to clean up because the event will be mainly linked to places table.Thanks |
|
|
|
|
|
|
Designing an event system