| Author |
Topic |
|
slapper
Starting Member
6 Posts |
 Posted - 2009-09-09 : 00:10:35
|
| I am having difficulty modeling the scenario below: A Championship is being held at a location, start_time, end_time. The Championship consists of several Events which also have individual start_times, end times. An Event is either a Soccer, Boxing or Surfing match storing unique details about each Event.My question is if an Event can be of three unique entities with unique fields. Would I create a table for each of these entities, then store the event_type_id (e.g. soccer) and the event_type_row (e.g. soccer 4) in the Event table. Is there a better way to model this data? CHAMPIONSHIP(id, location, start_time, end_time);EVENT(id_type, id_type_row CHAMPIONSHIP_ID, start_time, end_time);SOCCER(id, goals, penalties, red_cards);BOXING(id, winner, ko);SURFING(id, point_score);Thank you, any feedback is appreciated. |
|
|
cranky franky
12 Posts |
Posted - 2009-09-09 : 23:27:38
|
couple comments and questions. It appears a Championship is in fact an event...correct? If so, this is pretty much like in an office where someone can be both an employee and a supervisor, or a coach and a team member, etc. You can do a self relation to an events table and not have to have a separate championships table...assuming the data for a championship is the same as that you collect for a regular event this gets rid of one table. Secondly, interesting idea with the types but I believe it has a flaw. Where are you getting the value for EVENT.id_type? ANd if that is supposed to reference one of the type tables, how are you going to do that? To my knowledge, you can't put only a table reference in a select, you need a field as well. Your scheme suggests you are trying to reference the table with the id_type and then the row with id_type_row. That will not work. Foreign keys are field values not tables. Here is one idea. Now I over do it on normalization as I'm afraid of the unkown addition.  Hence, I would change the scheme a bit and build an "events" table as above, with events details, then a "type" table with the types and a "results" table with results. I would make my fields in the results table such that mutiple events could use the same fields with the ability to be NULL if that field does not apply. You then make linking tables with events and types to get the combinations you want. This would allow you to have infinite types, events, results, etc. example below. the category field in events id's the championship events with C being championship and R being regular event. With this scheme you could have Q for quarter finals, S for semi's, etc. Then do a self join for those categories. types(name, id)events(id, category, start, end, location)event_types(id, event_id, type_id)results(id, event_types_id, winner, win_pts, loser, lose_pts, ko, penalties,reds)so for a soccer championship event you'd have say:types(1,soccer)events(10, C, 7:00, 9:00, USA) event_types(5,10,1)results(12,5,usa,1,trinidad,0,NULL,0,0)your selects build the combo's you want with the amount of detail you need..nothing is left out if needed and way easier to update/modify. If you wish to keep the table number small, you could combine the results with the types, remove event_types and get similar to above - the type field is a text field with the name of the type. events(id, cat, loc, start, end)results(id, event_id, type, winner, loser, win_pts, lose_pts, ko, penalties, reds)using the same data as above:events(10,C,usa,7:00,9:00)results(12,10,soccer,usa,trinidad,1,0,NULL,0,0)anyhoo my two cents!"Let a complex system run long enough and something interesting is bound to happen." |
 |
|
|
|
|
|
How to model this data?