Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    193

    Unanswered: How should I set up this table?

    This is more of a general db question pertaining to design of one specific table as opposed to a syntax issue.

    In the course of a personal project I am attempting to build a database to capture data for a sports team. When it’s completed I want to do the standard inputting and updating of records and so forth. The question I have is in more in the area of designing a table(s).

    If I have a softball team that has veteran players that have played intermittently for the team. If a player plays in non-consecutive seasons and wears more than one uniform number while they play, this poses a problem. For example, I wish to be able to post a player’s data, name, height, weight, etc when a user visits our webpage(not up yet) and clicks on a player. If she has more than one number in the 5 seasons she has played it is a bit odd to post both uniform numbers. But compared to the idea of adding uniform number as part of the primary key, an idea I must say I don’t like at all, I feel it is the best alternative. I am therefore considering a table called players that has the name, position, college team attended if any, and something like uniform_number_start_date1, uniform_number_end_date1, uniform_number1, uniform_number_start_date2, uniform_number_end_date2, uniform_number2, etc for periods they wore their numbers and the numbers themselves. I assume that corporations that have employees work for them and have multiple durations must have a similar setup. Is this a good way to handle this data? Thanks again everyone.

    ddave

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It's more than 1 table

    Table 1 is Person

    Put all demographic info that belongs to a person
    Name, DOB, Height, weight(Are you sure the girls are gonns give you that?), College, ect

    Table 2 Player
    Year (Usually a season right?)
    Name
    DOB
    Team
    Number
    Position
    Batting Average (This is derived from...)

    Table 3 Game Stats
    Year
    Game Date
    Home Team Name
    Away Team Name
    Player Name
    DOB
    stats

    Table 4 Game Day
    Year
    Game Date
    Home Team Name
    Opposing Team Name
    Home Team Score (Derived)
    Away Team Score (Derived)

    Table 5 Teams
    Team Name
    ect
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Posts
    193
    Hi Brett,

    I did not separate the "player" and the "person" but either way, I still see the same problem. If I have one player with 2 or even 3 uniform numbers throughout her career, that means that I have to have an equal number of unique records for each instance unless I am not understanding you. Below is a rough idea of my model. Yes I do realize I need more than one table for the entire thing to work. My question was specific to the uniform number issue and the best way to handle it. Thanks again.

    ddave

    =========

    Stats_Table
    Id(pk)
    Player_id(fk)
    All the stats.......

    Player_Table
    Id(pk)
    Game_id(fk)
    Fname
    Lname
    Height
    Weight
    College
    Uniform_number <============this is the issue

    Game_Table
    Opponent_id(fk)
    id(pk)
    Season
    Week_number
    Date
    Home_Away_flag
    Our_score
    Opponent_score

    Opponent_Table
    Id(pk)
    Team_name
    Coach_name

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Uniform belongs in my table 2.

    It needs to be based on a season...if they change in a season, then it's a problem....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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