SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Guru
    Join Date
    Feb 2002
    Location
    NZ
    Posts
    620
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Probably obvious question, any db Guru please

    I have a db that is for sports.

    In one table (Teams) I could have a column (sport) in the column would be entries:

    Rugby
    Rugby League
    etc

    Now for example I would have the super 12 teams in the table (thats 12 teams)
    So my table would look like:

    rugby :: Crusaders
    rugby :: Highlanders
    rugby :: Hurricanes

    As you can see rugby would be in the table 12 times.
    With Rugby League, Rugby League would be in the table (I think) 14 times.
    I can never imagine a point where there would be any more than 20 duplicate entries.

    So heres my question:

    Should I build a table : Sport
    With id and sport columns

    Then build another table: Teams
    With sportid, team

    The Sport table would only contain at the most 20 sports (an incredably small table) and add to the amount of selects I would have to call on (now querying 2 tables) is it worth it to remove the duplicates from the Teams table ??

    And how much difference will it make toward stopping my db from becoming corrupt ??

    My guess is not to worry about the duplicate entries as there is so few, but I would rather be told by some-one that knows.

    Thanks in advance
    Last edited by Motivated; Jun 22, 2004 at 02:02.

  2. #2
    SitePoint Guru dale_burrell's Avatar
    Join Date
    Aug 2002
    Location
    Wellington, New Zealand
    Posts
    861
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Always build tables for duplicate information even if there are only 3 entries (2 can be done with a bit field).

    Otherwise you are breaking all the rules for relational databases and, while it is OK to break the rules from time to time , this could lead to all sorts of problems, a single spelling mistake looks like you have a new sport type, the need to know something specific about the sport in future (i.e. how many members to a team!!!) is very difficult without a seperate table.

    Rule of thumb, if you have 2 possibilities use a bit field, if you have 3 or more use a seperate table, its not even worth the time taken to consider which is the best option.
    If you aren't living life on the edge
    - you're taking up too much space
    Creative Dreaming Ltd / Ask The Local / Amanzi Travel

  3. #3
    SitePoint Guru
    Join Date
    Feb 2002
    Location
    NZ
    Posts
    620
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, completely different information to that that I have had in the past (not from this forum).

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i've done it both ways

    what if instead of going "up" from team to sport, you want to go "down" from team to category

    crusaders
    highlanders
    wrestlers -- men
    wrestlers -- women
    hurricanes

    the sql to join a sport table to the team table is about the same as joining the team table to the gender table

    and what if i'm the only one assigning names, and i know there's never going to be more than twenty of them?

    yes i have "hidden" a 1:m relationship inside a name

    but i also appreciate the wisdom of knowing when it's "not even worth the time taken to consider which is the best option"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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