Database Design - One large table vs Many smaller tables.

Discussion in 'Programming & Software Development' started by MistaKa0s, Nov 1, 2005.

  1. MistaKa0s

    MistaKa0s Member

    Joined:
    Nov 14, 2002
    Messages:
    51
    Hi Guys,

    I was wondering whether any DB gurus can help me with a DB design question that I have. I'm currently looking into designing a database for a little app that I have in mind, but I'm a little unsure whether I should have one large table or many smaller tables, which has basically the same schema.

    Say I'm looking at recording the daily temperature (high/low/average etc) from many locations, would it be better to :

    1. Put all the data into one table with all the location information in it.

    Date, Low, High, Average, City

    2. Separate the data into separate tables which represent cities/suburbs (and have another table tracking all the cities and the table name).

    Table City
    Date, Low, High, Average

    Which is the best approach to take? If I take option (1), there'll be performance issues later on (say when I've got a million+ rows and I'm only looking at a particular city).

    But if I take option (2), then I'll have many tables (hundreds/thousands), but table scanning a particular city would be relatively easy.

    I'm inclined towards option (2), but I'm wondering whether that is the correct option? Are there any performance issues with databases having lots of tables within them? I'm looking at using MySQL, but am also looking at more databases in general

    Thanks!
     
  2. phreeky82

    phreeky82 Member

    Joined:
    Dec 10, 2002
    Messages:
    9,510
    Location:
    Townsville
    option 2 is real bad

    well you could just go option one, or alternatively instead of storing the city name, store a city ID number, and then have another table with the city ID number as the key and also have the city name, and any other city details you wish to store

    edit: if you're really worried about performance, try and setup a test table with random entries and see how quick it is. i think you'll be amazed at how quickly something like mysql will separate that many entries based on a single field like that.
     
    Last edited: Nov 1, 2005
  3. NormM

    NormM Member

    Joined:
    Jul 2, 2001
    Messages:
    1,867
    Location:
    Beverly Hills
    Maybe have two tables. One with the Location names and the other with the data..
     
  4. n000b

    n000b Member

    Joined:
    Jul 4, 2002
    Messages:
    2,489
    Location:
    Melbourne
    That's what I would do - have a table that stores the city names, then in the temperatures table, just put in a field that links back to the ID of the city that it correspondes to, and use JOINS to get the data.
     
  5. Elyzion

    Elyzion Member

    Joined:
    Oct 27, 2004
    Messages:
    7,193
    Location:
    Singapore
    A normalized database.

    Option 1 will just get bloated and slow because of it getting large fast.

    Option 2... thats the first time ive ever heard such a suggestion so i donno what to say to that... except its a bad idea.
     
  6. OP
    OP
    MistaKa0s

    MistaKa0s Member

    Joined:
    Nov 14, 2002
    Messages:
    51
    wow thanks for the quick replies.

    I kinda guessed option 1 would be the better one (since it's one I've seen done everywhere), but just for interest sakes why is option 2 bad? Namely the large number of tables?

    I was planning on normalising option 1, but really there's only the date and the city to normalise.(that's just putting the repeated data into another table to avoid duplicity, yes?)
    It is wise to normalise the date? or should I just leave it in there?

    The reason why I thought of option 2 is because putting it all into the one table means that it'll become quite large overtime. I thought splitting the table up into smaller tables would be keep the sizes down, especially if I don't need all the data at the same time. (eg. only looking at specific cities)
     
  7. Elyzion

    Elyzion Member

    Joined:
    Oct 27, 2004
    Messages:
    7,193
    Location:
    Singapore
    Table 1: City
    Table 2: Suburb
    Table 3: SuburbInCity
    Table 4: (i dono, call it recordedweather?)

    Table City
    -------
    CityID
    Name
    Active

    Table Suburb
    -------
    SuburbID
    Name
    Active

    Table SuburbInCity
    -------
    SuburbInCityID
    SuburbID
    CityID
    Active


    Table RecordedWeather
    -------
    RecordedWeatherID
    SuburbInCityID
    High
    Low
    Average
    Date

    Depends how picky you wanna be for normalization... Removing repeated data will make your database smaller. If you were to expand it 1 step futher and say, record all the data for each State, and Country.

    In a month recording each day every 6 hours. The data would grow very fast, even if you only did Australia. The different in access time between a database that is 100mb (normalized) and 4gb (unnormalized) would be quite a bit, specially if you were having multiply people accesing it at once.

    If you were to normalize it, its future proof. If you don't and your website is popular, you have screwed yourself for having a history because to take 100,000 records and normalize them would take for ever.
     
  8. fox1

    fox1 Member

    Joined:
    Jun 28, 2001
    Messages:
    3,084
    Location:
    Brisbane
    Last edited: Nov 1, 2005
  9. Elyzion

    Elyzion Member

    Joined:
    Oct 27, 2004
    Messages:
    7,193
    Location:
    Singapore
    I donno, i was just to assume if you were to do it world wide, the same suburb would popup more than once.
     
  10. SkiNLaB

    SkiNLaB Member

    Joined:
    Dec 23, 2001
    Messages:
    709
    Location:
    Sydney's Inner West
    But would still be seperated by a unique suburbID
     
  11. thesuperav

    thesuperav Member

    Joined:
    Sep 16, 2002
    Messages:
    89
    Location:
    Brisbane
    I think option 2 is what database people call horizontal partitioning.

    For option 2-
    Are you planning to support user added cities, or do you know all the cities you'll ever measure up front? Because if users need to add new cities continuously, then I don't think it is good practice to be creating tables in your database on the fly.
     
  12. Adeptus

    Adeptus Member

    Joined:
    May 8, 2002
    Messages:
    758
    Location:
    Adelaide, SA
    Don't have to look worldwide to find suburbs with the same name... but there's no more than one with that name in each city.
     
  13. OP
    OP
    MistaKa0s

    MistaKa0s Member

    Joined:
    Nov 14, 2002
    Messages:
    51
    Thanks for the replies guys, I've read some more into horizontal partitioning and it's exactly what I want to do.

    I'll do some more research to decide what I need to do! I'll be adding new tables in myself (administratively) and I don't think I'll be doing that very often. I guess I got to find out whether mysql (or any other db) has any restrictions on the number of tables.
     

Share This Page

Advertisement: