> 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
