murray... it may have been helpful to the guy to also give him an idea of your tbl structure. i think you're talking about something like: tbl schema EvalTBL -id -UserID -ScoreTypeID ScoreTBL -id -ScoreType table ScoreType could/would have as many different categorites as required. table EvalType would have a scoreTypeID (from the ScoreTBL) for each userID. each user could have multiple scoreTypes in the EvalTBL... -bruce -----Original Message----- From: Murray @ PlanetThoughtful [mailto:lists@xxxxxxxxxxxxxxxxxxxx] Sent: Saturday, September 24, 2005 11:49 PM To: 'blackwater dev'; php-general@xxxxxxxxxxxxx Subject: RE: serialize > I have an app that stores evaluation scores so I have 30+ values all > within a certain range, currently in the db, each of these values has > it's own column: > > Table test > id > user_id > motivation > caring > personal_characteristics > creativity, > ...etc. > > If the client decides they want to trap more characteristics, it > requires changes to the table structure and the table quickly gets > large with 30+ columns. I was thinking of just compacting all of > these down to one column and then using serialize/unserialize and > storing an array of the test scores....is this the best way?? Hi, This has less to do with PHP (though it will impact on your code) and more to do with database design principles. >From what you describe, you have a denormalized table. Ie, every score value has its own field for each thing being scored: Id, score1, score2, score3, score4..., score30 1111, 23, 18, 12, 36...., 38 1112, 45, 12, 62, 25...., 73 A more normalized representation of that table would be: Id, scoretype, score 1111, 'score1', 23 1111, 'score2', 18 1111, 'score3', 12 1111, 'score4', 36 .... 1111, 'score30', 38 1112, 'score1', 45 1112, 'score2', 12 1112, 'score3', 62 1112, 'score4', 25 .... 1112, 'score30', 73 Adding a new score type for each id is then as simple as inserting rows for the ids with a new 'scoretype' value, meaning that no change of the actual table structure is required. To retrieve the scores for any given id in your PHP code, you'd do something like: $sql = "SELECT scoretype, score FROM scores WHERE id=1111"; $rs = mysql_query($sql); while ($row = mysql_fetch_object($rs)){ $scores[$row->scoretype] = $row->score; } mysql_free_result($rs); print_r($scores); It might be helpful to you to Google on the topic of database normalization. Here's a link from the MySQL site that gives a brief introduction to the topic. http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html Hope this helps. Much warmth, Murray --- "Lost in thought..." http://www.planetthoughtful.org -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
