I'm working on the MySqL db for a site that keeps track of members, teams, and bouts for a league of a new sport some friends came up with. I'm ok with dbs but I've never had to deal with one this big before and I know that there are things in it that are wrong but I can't seem to get them all straightened out at once. Here's what I have for the tables so far:

________________________________________________

Table 1: Users
WPL #(Primary key)
Last name
First name
Middle name
Gender
Address
City
State
Zip
Phone
Email
D/o/B
Parent name (if under 18)
Member directory – this will be a yes/no if they want to be listed in directory
Password
Membership Start date
Membership type (foreign key from membership table)
Paypal transaction # if applicable
Membership Expiration
Membership status: active, pending, expired, upgrade, banned, new
Rank (foreign key from rank table)
Age division (foreign key from age division table based on D/o/B)
Bouts - (basically total times their WPL# appears in lancer1 or lancer 2 columns of confirmed bouts in bouts table)
Wins – (basically total times their WPL# appears in winner column of confirmed bouts in bouts table)
PTCL # (foreign key from team table)



Table 2: Team data
PTCL# (primary)
Group ID (foreign key from team group table)
Team Class (foreign key class from team classes)
Team Age Division (foreign key from age divisions)
Team Status: active, inactive
Green (foreign key WPL# from users)
Green status: active, pending, ineligible
Red (foreign key WPL# from users)
Red status: active, pending, ineligible
Black (foreign key WPL# from users)
Black status: active, pending, ineligible
Purple (foreign key WPL# from users)
Purple status: active, pending, ineligible
Blue (foreign key WPL# from users)
Blue status: active, pending, ineligible
Grey (foreign key WPL# from users)
Grey status: active, pending, ineligible
Team Contact (foreign key WPL# from users)
Contact status – confirmed or pending


Table 3: Team Groups
Group ID # - (primary key)
Group Contact (foreign key - WPL# from users table)

Table 4: Team Classes
Class ID (primary key)
Minimum Rank (foreign key Rankid from rank table)
Maximum Rank (foreign key Rankid from rank table)

Table 5: Rank
Rank ID (primary key)
Rank name
Min bouts
Max bouts
Tourney bouts (a certain # of bouts must be in tournament not practice)
Min Victories
Max Victories

Table 6: Age Divisions
Division (primary key)
Minimum age
Max age

Table 7: Bouts
Bout ID# (primary key)
Date (timestamp)
Lancer 1 ( foreign key WPL# from users)
Lancer 1 Score
Lancer 2 ( foreign key WPL# from users)
Lancer 2 Score
Winner ( foreign key WPL# from users)
Recorder ( foreign key WPL# from users)
Bout type: practice, tournament, etc
Status: pending, disputed, or confirmed

Table 8: Membership types
MemberType ID (primary key)
min Rank
Max Rank
Price
Duration
(there will probably be more, but I don't have all the data that will need to be included in this one yet)

_______________________________________________

The main 2 tables that i'm sure there is something wrong with are Users and Teams.

In users it just seems too big like there has to be something i've missed in splitting it out. Also I know that theoretically I shouldn't have bouts and wins in there when I can calculate it from the bouts table, but since the users rank and membership status (higher ranks have a different membership) depend on the bout totals and the member's eligability for a specific team depends on their rank, I think that calculating the bouts and wins every time I need any of the other dependant data would be bad.

In the teams table, the colors are the roles a user may take on a team. One of each per team, so they're not really redundant but i'm not sure about them anyway.

I also somehow need to keep track of team contacts inviting someone to join fill a vacancy on their team, and if the invite isn't confirmed or denied in 1 week it expires. Theoretically that's what the pending is for on color statuses in the team table but I don't have a way to keep track of the 1 week yet.

I know I've probably left out serveral pieces of info that would make this make sense to anyone who hasn't been immersed in it for a month, but at the moment, i'm not sure what they would be.

Thanks for any replies....

Megan

Suggestion: Team Group should be an associative table from User (WPL #) and Team (PTCL#). With this you can then have role code within this table (what you state as 'colors'). This is a better design because if another 'color' is added in your case you would have to alter a table. With role code suggestion, you just add an allowable value (role code would be an FK from a table containing codes and descriptions). Also, I would have an association table between Bouts and User.

Allow for flexibility by making when you need to define multiple relationships you are defining them as rows not columns.

Team group doesn't have much to do with the members of the team. Team Groups are to tie together several teams that practice together or teams that are all made up of students of the same teacher. Something like that. But yeah....I was thinking i'd probably need to put the roles into an inbetween table even though the odds of them adding another color are right up there with everyone deciding there will be 6 players on the court at a time in basketball.

I'm not sure what you mean about having an association table between Bouts and User. Rather, I can see where it might be logically useful....but structured how?

Megan

Your user table is definatly too big, alot of it can be taken away. Perhaps make a Memebers table with information about their membership linked to a user via the user key.

eg

Membership(User_id, join_date, expiry_date, status, type).

now you can remove all of the membership fields from the user table.