Database Structure - Items from multiple tables

Question

I am creating a database for storing wrestling events and have hit a wall with how to structure my database. Appreciate anyone's assistance.

I have the following tables:

tblPeople that will contain all of my wrestlers.

tblPeople
  - personId
  - personName 
    #eg: "211", "Dean Ambrose"

tblTeams that will contain a specific tag team

tblTeams
- teamId
- teamName
  #eg: "13", "Shield"

tblPeopleInTeams will relate people to teams

tblPeopleInTeams
- peopleInTeamsId
- teamId
- peopleId
  #eg: "1", "13", "211"

tblMatches will contain match details and needs to reflect the people or teams involved

I am unsure how I can structure tblMatches so that it can reference either a person or a team. An example would be that a match could have either "Dean Ambrose" or "Shield" as a competitor.

I could do:

tblMatches
- matchId
- peopleId
- teamId
  #eg: "412", "211", NULL (for Dean Ambrose)
  #eg: "412", NULL, "13" (for Shield)

But to use this I would have to only populate one of the fields and use conditional logic in my application to determine the type of entry. I imagine there is a better way to do this but I can't think of how. Any thoughts please?

Thanks, Jordan.

score 2 · Accepted Answer · edited May 23 '17 at 12:11

2

You may use generalization pattern in database design.
Having a base table for person and team called wrestler with 1:0 relation.
Something like this:

For Match designed between two competitors. enter image description here

For multiple competitors:

edited May 23 '17 at 12:11

Community

1
1

answered Dec 19 '13 at 10:03

Mohsen Heydari

7,256
4
31
46

Hi Mohsen, thanks for the reply however wouldn't this just move the need for NULL values down another level? How would the wrestlers table be structured? – Jordan Windebank Dec 19 '13 at 11:03
Hi, there will be no nulls. wrestler will have a name and a type field the type will show if a wrestler is a person or a team, the pk of wrestler table will be the pk of team and person, per record in wrestler the will be on record in person OR team table. – Mohsen Heydari Dec 19 '13 at 11:07
Ahhhh I get it now. Thank you Mohsen, I think this will work perfectly. Will give it a go and confirm shortly. Thanks again! – Jordan Windebank Dec 19 '13 at 11:36

score 0 · Answer 2 · answered Dec 19 '13 at 06:59

Seeing as a Match can consists of several people, and/or several teams, I'd properly make a "Participant" table to associate a match with people and teams. Something like you're already mentioning doing with Matches, but I wouldn't put it into Matches as there will be a lot of "match" based data as well, which does not need duplicated records, such as locations, times, etc.

Also because then it'll be easier to mark the "Winner" on a Participants table or other data associated.

So something like:

tblMatches
-matchId
-other data such as location, time, event, or what you think fit.

and then change your tblMatches to

tblParticipant
-matchId
-peopleId
-teamId
-isWinner

Now, granted, I'm no wrestling expert, so the finer mechanics of team makeup I don't know, but a quick search told me that your guy Dean Ambrose is a member of Shield, so instead of having two rows for him in your e.g. - you could just do

#eg: 412, 211, 13 (for Dean Ambrose when participating with Shield) and then use the NULLs when he's participating solo.

So basically - to make a short story longer, I'd interject a level above your tblMatches and rename matches to participants or similar naming which fits your model.

Thanks Allan. Adding the intermediary table makes sense. If I am adding a team then I wouldn't want to include the individual as I'd pull this from tblPeopleInTeams, so it would need to be one or the other. Is there a way to add a rule to the database to only allow one of these or would that need to reside in the application layer? I'd want to display this on screen as "Shield (Dean Ambrose, Seth Rollins & Roman Reins) vs. Daniel Bryan". — Jordan Windebank, Dec 19 '13 at 07:13
You can set constraints up which enables you to control stuff like that, yes. — Allan S. Hansen, Dec 19 '13 at 07:27

Database Structure - Items from multiple tables

2 Answers2