September 25, 2010 at 3:28 pm
Hello, this is my first post, as I'm struggling with the design/modeling of the database for this project. Your help is greatly appreciated!
Hopefully I can explain this such that you don't need a knowledge of wrestling (I didn't have one myself when I began)!
The project is for a wrestling tournament management system. Thus, there will be a Tournament
Tournaments are divided into weight classes (ex. 103,112,119,125,130,135,140,145,152,160,171,189,215,285). And each weight class is assigned a TournamentBracketID (the type of tournament bracket being used for that weight class).
The tricky part is that these weight classes need to be saved and loaded as "groups". Each group will have a name. The weight classes listed in this example above are known as the "default high school weight class group".
There will be a few of these default preset weight class groups that the users can select, but a user also needs to be able to create custom weight class groups, as well as change the order of the weight classes within the weight class group.
Therefore, when a user creates a tournament, they can select a preset weight class group or create a new custom one, and in either case they will be able to change the order. Once the weight classes are saved/associated with the tournament, the name of the group needs to be able to be found, regardless of the order. So, multiple users might be using the "default high school weight class group", in different orders, but I need to be able to link it back to the preset to retrieve the name.
The custom weight class groups created by the user need to be saved so they can use them again in the future, for a new tournament... like their own list of presets.
Once the tournaments are associated with a weight class group, each weight class in the group is assigned a bracket ID. Then the bracket is broken up into matches. So each weight class needs an ID to associate the brackets with as well.
From there it's a bit more straight-forward design-wise, but if anyone has experience or ideas on translating both single-elimination and double-elimination tournament brackets into a database, it'll probably be very helpful down the road as well!
I hope I explained that clearly enough, and hopefully someone finds the fun in solving such an issue and can help me out! Thank you very much in advance!
Robert III
September 27, 2010 at 6:00 am
wargy2 (9/25/2010)
Hello, this is my first post, as I'm struggling with the design/modeling of the database for this project. Your help is greatly appreciated!
Would you mind in starting by identifying your Entities and Relationships then describe the model in ER terms?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 28, 2010 at 6:28 am
What Paul said, is absolutely correct. You can't do much without determining the entities or relationships. That should be the first thing you do, or sketch out on paper so you can better envision the design. Your problem doesn't appear to be overly complicated. I think your approach to solving the problem needs better focus.
Here's what I did as a preliminary design:
Athlete
AthleteID PK
Name,
School,
Weight,
etc..
WeightClass
WeightClassID PK
WeightFrom
WeightTo
IsDefaultWeightClass
CustomWeightClass
CustomWeightClassID PK
WeightClassID (FK from WeightClass table)
UserID (FK from TournamentUser table)
TournamentUser
UserID PK
Username
AthleteClass
AthleteID (FK from Athlete table)
WeightClassID (FK from WeightClass table)
Tournament
TournamentID PK
Bracket
BracketID PK
This should get you started.
September 28, 2010 at 9:39 am
Thank you for the replies. I'm familiar with data modeling, but not "ER speak"... maybe it's the same? Anyway, determining that part was where I was struggling, and I didn't want to write out what I had thus far because perhaps my solution was flawed from the start.
I appreciate the preliminary design... thank you! It wouldn't quite work, but maybe I didn't explain well enough. I'll follow your design and show what I settled on, and seems like it should work out.
Athlete
AthleteID PK
Name
SchoolID FK
...
Tournament
TournamentID PK
Name
Date
HostSchoolID FK
WeightClassGroupID (links to WeightClass.GroupID)
...
WeightClass
WeightClassID PK
Weight
Ordinal
GroupID
TournamentBracketID FK
WeightClassUserWeightClass
WeightClassGroupID PK FK
UserWeightClassGroupID PK FK
UserWeightClass
UserWeightClassID PK
UserID FK (user = admin setting up tournament, not athlete)
Weight
Ordinal
GroupID
UserWeightClassName
UserWeightClassGroupID PK FK
Name
TournamentBracket
TournamentBracketID PK
BraketID FK
...
TournamentBracketMatch
TournamentBracketMatchID PK
TournamentBracketID FK
MatchID
Points
...
TournamentSchool
...
I left out a few tables that should be obvious, and then there are a bunch of tables dealing with the "settings" for each tournament, tournamentbracket, and tournamentbracketmatch.
The tricky part is that the weight classes are in groups of discrete weights, not a range... maybe I didn't explain that well. I used those GroupIDs, which I'm not positive is best practice or not. Then I used that WeightClassUserWeightClass table to link the final weight class groups used in the tournament (regardless of the order of the weights) with the original UserWeightClass group that it came from so that I can grab the user-specified name from UserWeightClassName.
Hopefully that makes sense... let me know what you think of my solution!
September 28, 2010 at 11:47 am
Where is GroupID coming from?
Is there a GroupTable?
If I understand then the "groups" have mixed weight classes in them. Essentially the event wants x Number of athletes in each group.
Do you have an example of the group?
Group1: Athlete_A, Athlete_B, Athlete_C,...
I think the athletes need to be assigned to a Group which I don't see. You could either add this to the Athlete table, or better yet add another table AthleteGroup. This will allow you to retain the athletes for future tournaments, if these athletes return.
The UserWeightClass table might also need TournamentID.
September 28, 2010 at 11:53 am
Sorry, perhaps it'd make more sense if in the WeightClass table GroupID was WeightClassGroupID and in the UserWeightClass table GroupID was UserWeightClassGroupID.
Each weight class (WeightClassID) has a TournamentBracket associated with it, but each Tournament is associated with a group of weight classes.
So, the grouping has nothing to do with the athletes. I realized I didn't really provide a link to associate the athletes with the tournament... I didn't get that far in the project yet. They will likely be in the TournamentBracketMatch table... AthleteAID and AthleteBID as FKs, perhaps.
The UserWeightClass table stores each user's (again, an admin, not an athlete) custom weight class groups... the same as the CustomWeightClass table in your example.
September 28, 2010 at 4:19 pm
wargy2 (9/28/2010)
Thank you for the replies. I'm familiar with data modeling, but not "ER speak"... maybe it's the same?
You are welcome.
For ER approach to data modeling please take a look at... http://www.agiledata.org/essays/dataModeling101.html
...then when translating from Logical to Physical model make it simple and go with the third normal form a.k.a. 3NF, as discussed in the same paper.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply