SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Guru ripcurlksm's Avatar
    Join Date
    Aug 2004
    Location
    San Clemente, CA
    Posts
    859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Storing as an array - Vs. - Storing in a relational table

    A car can have many attributes. Which scenario is better?

    Store in a relational table

    Code:
    ===============================
    car_table
    ===============================
    c_id	|	car_name
    -------------------------------
    1	|	corvette
    2	| 	civic
    
    
    ===============================
    attribute_table
    ===============================
    a_id	|	car_name
    -------------------------------
    1	|	fast
    2	|	efficient
    3	|	stick
    4	|	automatic
    5	|	red
    6	|	silver
    
    
    car_attributes
    ===============================
    c_id	|	a_id
    -------------------------------
    1	|	1
    1	|	3
    1	|	5
    2	|	2
    2	|	4
    2	|	6
    or just store an array in the car table like this:
    Code:
    ==========================================================
    car_table
    ==========================================================
    c_id	|	car_name	|	attribute
    ----------------------------------------------------------
    1	|	corvette	|	array(1,3,5)
    2	| 	civic		|	array(2,4,6)

    Scenario 1 would have more complex table joins, but would establish the ground rules using the table's naming conventions. Scenario 2 seems like the better option because it is easier to read and write to and rely s on the programming language to do the heavy lifting. For example, to read the array's stored, I would have to use a script function to determine what array=1 is and give it a value beforehand using a script. Where the first scenario, I would just pull the value from the table for array=1.

    Thoughts?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    do a search for first normal form

    there is a very good reason why you should never put more than one piece of information into each column value -- you cannot search easily on it

    which cars are red? you have to read the entire table, and use the programming language to discombobulate your array

    reading the entire table is like using a flat file -- inefficient
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru ripcurlksm's Avatar
    Join Date
    Aug 2004
    Location
    San Clemente, CA
    Posts
    859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    as always, thanks Rudy!

  4. #4
    SitePoint Guru
    Join Date
    Jun 2006
    Posts
    638
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    #1 would be the "best" way to do this, since you can do a select for a car with a certain atribute. (That's what first normal form is for...)

    #2 would be the "best" way if you want to code it faster, use more CPU on the web server rather than the DB server, and if you will NEVER try to find all cars with X attribute.

    I vote for #1

  5. #5
    SitePoint Guru ripcurlksm's Avatar
    Join Date
    Aug 2004
    Location
    San Clemente, CA
    Posts
    859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks vali-- can you suggest an instance where storing an array would be appropriate? any example, if any --

  6. #6
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,875
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by ripcurlksm View Post
    thanks vali-- can you suggest an instance where storing an array would be appropriate? any example, if any --
    You would only convert a database to use that method if the database is running too slow because of the extremely huge number of queries AND there are no queries whatever that need to search on that information. The other 99.9999% of the time you'd use option 1. Also you would never set it up to use option 2 in the first place, you'd only ever convert to it if it were needed to improve the efficiency of the database when the load is becoming too heavy and you've exhausted the alternative possibilities.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  7. #7
    SitePoint Guru ripcurlksm's Avatar
    Join Date
    Aug 2004
    Location
    San Clemente, CA
    Posts
    859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks felgall, you've always been a big help, ive read a lot of your responses to other threads -- much appreciated to all


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
  •