TalkPHP - View Single Post

delayedinsanity · 08-30-2010, 11:12 PM

The only problem with storing serialized or imploded arrays in your database is that you complicate searching on those fields by a factor of ten. It goes up from there depending on the type of information being stored; in this case you could do a LIKE search and still get relatively accurate information, though in the process you eat your own performance.

mysql Code:

CREATE TABLE `user_colors` (
`user_id` BIGINT( 20 ) UNSIGNED NOT NULL,
`color` ENUM( 'red','blue','green','magenta' ) NOT NULL,
INDEX ( `user_id` ),
) ENGINE = MYISAM

The beginnings of a user meta information table; you could easily switch to an NVP setup or simply create additional columns as necessary. Now with two simple queries you could easily pull up the information for a specific user, or all users who list the color blue, or combine both searches in one. Update your enum field to add more colors, or switch it for a varchar if the need becomes too great.

08-30-2010, 11:12 PM	#3 (permalink)
delayedinsanity is cute and cuddly Join Date: Mar 2008 Location: Vegas, Baby Posts: 963 Thanks: 31	The only problem with storing serialized or imploded arrays in your database is that you complicate searching on those fields by a factor of ten. It goes up from there depending on the type of information being stored; in this case you could do a LIKE search and still get relatively accurate information, though in the process you eat your own performance. mysql Code: CREATE TABLE `user_colors` ( `user_id` BIGINT( 20 ) UNSIGNED NOT NULL, `color` ENUM( 'red','blue','green','magenta' ) NOT NULL, INDEX ( `user_id` ), ) ENGINE = MYISAM The beginnings of a user meta information table; you could easily switch to an NVP setup or simply create additional columns as necessary. Now with two simple queries you could easily pull up the information for a specific user, or all users who list the color blue, or combine both searches in one. Update your enum field to add more colors, or switch it for a varchar if the need becomes too great. __________________ uʍop ǝpısdn sı ɹǝʇndɯoɔ ɹnoʎ sıɥʇ pɐǝɹ uɐɔ noʎ ɟı