Results 1 to 4 of 4
  1. #1
    Join Date
    May 2007
    Posts
    80

    seperate table OR duplicate data

    I've got a database containing information on Tennis. One aspect is Season which is a name. (it appears to be the same as a calendar year but it is not)

    I have determined that for example a Tournament is only unique in combination with a Season. Same with matches which are unique inside a Season + Tournament + Round. So Season appears everywhere and is used in 90% of all queries.

    Now i have a choice:
    - Put Season in a table of its own with an ID and reference that ID.
    - Simply put Season as a char or varchar in each table where applicable.

    If i use a seperate table for Season names then i use the least diskspace but i will get a JOIN statement with this table in just about 90% of my queries.

    If i duplicate the name inside other tables then i use more diskspace but my queries are simpler and do not need a JOIN. ( the primary keys on those tables do get to contain a char or varchar rather than a couple of int's and i'm not sure on the impact of that )


    My thinking at the moment is:
    as the database isn't that large i'm not too concerned about diskspace and i would prefer simpler queries. So i put Season is each table as a string and use a bit more diskspace but save the resources used by a JOIN in many queries.

    Anything wrong with my thinking ?
    ( be gentle )

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your thinking is excellent

    there are other threads going on at the moment regarding whether to use an id or not, but you don't really need to read them

    in your case, use the Season name itself, and not a numeric id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That's a decent analysis of the issue. We are discussing these points now here:
    http://www.dbforums.com/database-con...db-tables.html

    Google surrogate Vs Natural keys if you really want to go to town.

    Personally, I think your current design sounds perfectly acceptable. How many rows likely in this database? I would have thought you are talking KB, maybe MB, disk savings - not really worth the effort eh?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    May 2007
    Posts
    80
    thanks,
    was just reading that thread and it does answer my question nicely ( should have done that first eh )

    Does come down to "space vs speed" ,
    actually in my case the top priority would be my own "human user friendlyness".

    As far as diskspace goes i expect maybe something like 10-15 Mb for the core database.

    So if i need to use a few Mb's more but as a trade off i get queries consisting of 10 lines in stead of 20 lines i'll gladly make the trade. (within reason)

Posting Permissions

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