SitePoint Sponsor |
|
User Tag List
Results 1 to 7 of 7
-
Nov 16, 2008, 00:08 #1
- 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
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?
-
Nov 16, 2008, 05:37 #2
- 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
-
Nov 16, 2008, 20:21 #3
- Join Date
- Aug 2004
- Location
- San Clemente, CA
- Posts
- 859
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
as always, thanks Rudy!
-
Dec 12, 2008, 18:18 #4
- 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
-
Dec 13, 2008, 14:15 #5
- 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 --
-
Dec 13, 2008, 14:52 #6
- Join Date
- Sep 2005
- Location
- Sydney, NSW, Australia
- Posts
- 16,875
- Mentioned
- 25 Post(s)
- Tagged
- 1 Thread(s)
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="^$">
-
Dec 13, 2008, 22:43 #7
- 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