Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004

    Red face help designing complicated db

    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
    Parent name (if under 18)
    Member directory this will be a yes/no if they want to be listed in directory
    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
    (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....


  2. #2
    Join Date
    Aug 2003
    Massachusetts, USA
    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.

  3. #3
    Join Date
    Jun 2004
    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?


  4. #4
    Join Date
    Jul 2004
    Membership Start date
    Membership type (foreign key from membership table)
    Membership Expiration
    Membership status: active, pending, expired, upgrade, banned, new
    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.


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

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts