Posted by "bruce" on 09/26/05 17:00

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@planetthoughtful.org]
Sent: Saturday, September 24, 2005 11:49 PM
To: 'blackwater dev'; php-general@lists.php.net
Subject: RE: [PHP] 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

[Back to original message]