I've been "wrestling" with this problem for a while and have been looking around for some place to ask some knowledgeable database people and then I ended up here. This has been a project I've been wanting to do for years but never understood databases and other programming enough to pull it off, until now.

Okay, basically, I understand what I need to do to add to MySQL databases, pull data out and display it, update it, delete it, etc, so that's all covered. No, my problem has been trying to figure out the best way to design the tables to store all the data that I'm going to be collecting to create my pro wrestling database. I've thought of MANY different ways of how to set up the tables to keep the data and best be able to search and store the information, but nothing seems perfect so far and even the best tables I've built are a bit "wonky" in how they work.

I'm going to try to explain this as quickly and efficiently as I can, so that everyone knows how I've been organizing it. Now, my ultimate goal is to create a table or likely series of tables to store all the information on let's say a pro wrestling company, like WWE, for example. I want to store all the results of all the matches of all the shows and pay-per-views, along with general stats of all the wrestlers in the company. Then I want to be able to search the full database in a ton of different ways, like how many times a certain wrestler has won a title, or list all the WrestleMania matches of all time, or show all the winners of the Royal Rumble, and TONS of other stuff like that. The problem with making a pro wrestling database is that with all the match types, stipulations, the numbers of wrestlers in each match, the number of ways a match can end, all the championships, and ALL of that is VERY difficult to keep track of and organize in a rational way.

The list of my current tables to show the best I've come up with, which STILL, as I said, is a bit "wonky" is as follows:

Wrestlers = list of wrestler stats, such as height, weight, name, debut date, nickname, and hometown (and I'm hoping eventually to be able to list all the wins, losses, and draws of all wrestlers, so hopefully I can pull that off too)
Titles = list of championships
Shows = list of all wrestling shows where all the matches have taken place
Matches = list of all matches with info such as match date, title ID number which links to the titles table, show ID number which links to the shows table, stipulation, and extra notes about the match
Results = list of every single wrestler in every single match, all listed separately with info such as match ID number which links to the matches table, wrestler ID number which links to the wrestlers table, and then result, such as win, loss, or draw

NOTE: Each and every table has a unique ID number for all rows, such as Match ID, Title ID, Show ID, Wrestler ID, etc

The current set-up works, in a way, but it's "wonky" in that you store the basic match info in the matches table, and then add in wrestlers to each match into the results table and just link them up with the Match ID and Wrestler ID numbers. It works, like I said, as it draws up the wrestler and show names, but it's just very odd to work with. I tried to normalize it sufficiently, but not TOO much, and it's just very difficult to figure out how to manage this whole thing.

My biggest problem is that with some matches being one-on-one, so only two wrestlers, and others having upwards of like ten to thirty wrestlers in one match, some in teams of various numbers, some all against one another in big battles, I'm not sure how to store the info of who was involved in each match, who won, and if a title was on the line whether it changed hands or was successfully defended or not. If it was simple, such as boxing or any other sport with ONLY one guy vs another guy, or one team vs another, it'd be SIMPLE, but this is very, very complicated, so I was just curious if anyone could help me figure out how to organize this info into well-designed tables, cuz as I've said, so far I'm pretty clueless.

Thanks for reading this lengthy post and any help would be greatly appreciated.

So its a variant on the age old school project of a sporting fixture

what do we have

well a wrester tabe, containing inforamtion relevatn to each wrestler (eg DoB, Gender, etc..)
a venue table, detailing wher eth epalce is, say capacity etc...
a fixture/event table detailing what event was held on what date and where (referring to the venue table)
a fixture details/event details/bout table which indentifies who fought who (referring to the wrestler table), where (on what bout referring to the event table) and includes the result.. depends on how deatiled you want to be
youcould have a bout details table listing what happened when.. eg round 1 wrestler A scored the following items......, Wrestler B..... if you went down that road you may want to consoider having a Wrestler Scoring table, identifying what scoring move earns what....

@healdem - Thanks a lot for the fast response, but as I said, a pro wrestling database is a lot more complicated than that. Your setup would work well for amateur wrestling, I think, as each match in the database would simply be listed as Wrestler A defeated Wrestler B to win the C championship, but I'm getting very lost when it comes to, for example, a tag team match where two guys defeated another two guys, or three guys beat three others, or handicap matches where it's one vs two or three, or even a battle royal where thirty men all fought one another and only one of them won. That's what I'm finding so hard to store as data, as I don't want to have each match have like dozens of columns for spaces for wrestlers if MOST of the time they wouldn't even be used, like with a simple one-on-one match.

After doing a lot of research on databases and normalization, I realized that it's much better to simply make all the wrestlers who wrestled in each match in their own separate table and then just link it back to the match they participated in, but then I'm having a hard time listing, for example, if that wrestler won a title, when in some matches when a title is on the line a non-champion can beat a champion, but by disqualification or count-out and NOT win the title, so having all that listed so it's easily searchable is turning out to be a pain. That's been the biggest problem and what I'm asking for is how to list all the wrestlers in each match and how to show which won a title or won a match but didn't win the title, and I want to be able to easily search for things like how many times someone has teamed with someone else, and then how many times they won with them, and how many times they lost, or how many times they faced each other as opponents, or even how to look up tag teams specifically if I have them all listed separately and not by their tag team names.

I realize this is a very complicated database, so that's why I came here to the dbForums to see if any of the great posters here could help me out. Again, thanks to healdem for the quick response and hopefully I cleared up my problem a bit. If anyone has anything to add, PLEASE do as this has been nagging at my mind for weeks and weeks now. Thanks very much.

ok so..
you have an event table
you have a bout table
you have a participants in bout table (associates a specific actors with a specific bout). by dropping this down into a intersection table you can have as many actors as required per side). I guess the intersection table would require something to identify which actor is on which team
if required you can identify the title winner in the intersection table
you could identify the winnuign team int he bout table

I dont think its a paticularly complex db though

@DestinedHero:

I guess you're turning a simple problem into a complicated one. You have to split the data definition from the rules (i.e., the tables from the "how-many-this" or "how-often-that"):
You have Events, Places, Matches, Wrestlers, Titles, Teams.
These are the main tables (i guess their purpose is self-explanatory, but i'll dwell into more detail if i have to).
Then you have the "Relations" tables: If a match can have an undefined number of players you have a MatchWrestlers table, if the teams are valid only for some matches or change at every match, you have the MatchTeams table, and so on.. this tables help you to join all the stuff in the main tables.

HTH

I will definitely try both of those out, each separate and maybe put together, but one of the issues I'm coming up with is how do I list, for example, when a champion loses to a challenger, but the challenger doesn't win the title because of a disqualification or count-out? Also, I like the idea of the "Teams" table, which should help out with some stuff, but for the MatchTeams table, do you mean to have a new matchteam for each and every match if it's a new team that has never teamed before? That's what I'm assuming you mean, and that should help, yes.

Now, with the winner and loser for each match, where should that be listed? In the Matches table? Or in each individual MatchWrestlers/MatchTeams table? And what happens if there is a draw, therefore technically no winners or losers? My old setup had a "result" for each wrestler in the MatchWrestler table with it being either "win" or "lose" or "draw" but again, I was having a problem figuring out how to differentiate between someone winning a match but not the title, and then that wrestler/team winning a belt and even how to show that the champion lost the title so I could search for that later.

I realize it's probably easier to set up than I think, but I've just been very confused trying to wrap my mind around all the various options of results and things that can happen in ALL the different types of wrestling matches that happen, so all the help so far is very much appreciated. Thanks a lot and I can't wait to read your responses to my latest post.

Let me clarify things a bit.

I don't really follow WWE or wrestling, so i might miss something, but here goes.
You said there are a lot of different types of match with different rules and so. You can have a MatchType attribute or so that you might use.

The match result and all the related info should go in the Matches table. The MatchesWrestlers table or the MatchesEvents table are simply to relate the Wrestlers to the Matches and Matches to Events, resp.
Of course the result value might itself be related to the MatchType attribute, meaning that some kind of matches have a certain range os possible outcomes but other kinds have other possible outcomes...

I hope i didn't confused you more