This is just to get some thoughts and opinions on how to handle my database.
Okay so say I have a table for scheduled wrestling events that looks like this:
id : (auto- increment, primary key)
event_name_id: (id of the event name from the event_names table)
event_label: (label of the event will either be the next incrementing number from the previous occurance of event or the year in which its taking place)
date_of_event: (date of which the event is taking place)
number_of_matches: (number of matches set for the event)
number_of_segments: (number of segments set for the event) (a segment might be a backstage interview for example or anything that isn't a match)
arena_id: (id of the area from the arenas table)
event_status_id: (id of the event status from the event_statuses table)
The next step after creating the event is setting up the matches and segments. My form is setup for a textarea for the show introduction, and an area for each of the matches which includes the following:
match type: a dropdown that has a list of match types (Singles, Tag Team, Triple Threat, etc.)
titles: a dropdown that has a list of available title belts that could be on the line (can have more than one attached) and an Add Submit button that adds the title to a UL.
referee: a dropdown that has a list of available referees
stipulations: a dropdown that has a list of stipulations that could be added to the match (can have more than one attached) and an Add Submit button that adds the stipulation to a UL.
wrestlers: a dropdown that has a list of wrestlers that could be competiting for the match (will have more than one attached) and an Add Submit button that adds the wrestler to a UL.
match writer: a dropdown that has a list of users with the selection being the user responsible for writing the results of the match after it has taken place.
match title: the title for the match NOT the title belt but the title ie Wrestler 1 vs. Wrestler 2
preview: a preview for hyping up the match
And for each of the segments is the following:
segment Writer: a dropdown that has a list of users with the selection being hte user responsible for writing the results of the segment
segment After: a dropdown that represents the Introduction, and each match which dynamically field because of it telling how many matches there are in the event
I'm trying to come up with as many ideas as the BEST way to store all this information.
Some variables that could be considered are how many characters are required for each type of match and possibly say its a tag team match and the characters are listed in the dropdown separate or they should be listed together or even if maybe for one event a 8 man tournament takes place where obviously the first round matches will be known but the second and third round would not be known to the people looking at the lineup card.
I also need to think about the the way the form for match and segment submission is done as well as the results of the event which includes keeping track of winners and how the winner was accomplished through a pin, dq, etc. I have a database table full of decision types. Also need to keep track of the winners with a Win, Loss, or Draw.
I also need to consider when a match is a title match and whether or not the title changed hands.
Any and ALL thoughts, suggestions, opinions are welcome.