fields vs rows which is quicker

Discussion in 'Databases' started by ignas2526, May 16, 2009.

0
  1. #1
    Hello,
    I need to store like 200 various values, from boolean to random size texts, this means what each value will have its own format.
    So which way will be quicker/less memory using: to create 200 fields for each value and one row with values, or to create 2 fields (name and value) and 200 rows?
    I will need to select all values every time, so if i use second way i will also need to perform while loop to create array with values from all fields.
    Thanks.
     
    ignas2526, May 16, 2009 IP
  2. freelistfool

    freelistfool Peon

    Messages:
    1,801
    Likes Received:
    101
    Best Answers:
    0
    Trophy Points:
    0
    #2
    I would do so reading on database normalization. You probably don't have 200 fields that are related to one specific key so if you normalize your database you'll find it easier to retrieve the data for each specific purpose. Normalization isn't that hard to do and it will save you a lot of headaches in the future.
     
    freelistfool, May 17, 2009 IP
  3. chisara

    chisara Peon

    Messages:
    141
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Given the amount of rows I would call this premature optimisation, Google tells me that is the cause of lots of bad things.
    Just store everything as varchar and cast if and when needed, you could combine this with a type column to know how to cast it back in your source datatype.
     
    chisara, May 19, 2009 IP
  4. 2beers

    2beers Well-Known Member

    Messages:
    195
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    110
    #4
    200 is not a big number
    probably you have something like a configuration database with 2 columns name and value. you should make with 200 ros. my opinion
     
    2beers, May 19, 2009 IP
  5. timarcher52

    timarcher52 Peon

    Messages:
    62
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #5
    If the 200 fields will stay pretty static, then I'd go with the 200 individual columns, especially if you'll be doing a lot of data mining off of the data. It makes it easier then to get aggregate views of the data, such as in the case of survey responses.
    If you dont anticipate aggregating the data heavily, or anticipate the values you have to collect changing frequently, then go with the table containing name/value pairs.
     
    timarcher52, May 21, 2009 IP
  6. ambition_09

    ambition_09 Peon

    Messages:
    7
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #6
    you should to check table care fully. If possible then try to reduce the number of field by normalization or break the table in more the one table
    And finally i suggest you should to go with 200 rows ..that will be a better and logical way.......
     
    ambition_09, May 21, 2009 IP
  7. 2beers

    2beers Well-Known Member

    Messages:
    195
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    110
    #7
    however if this is a configuration database i would suggest to create a condiguration file and put that values in an array. i think it's must faster because you don't have to make a select from database every time someone visits a page. but it all depends what do you intend to do :)
     
    2beers, May 21, 2009 IP