Hi Fredrik,
--- Fredrik Davidsson <fdqps@alfaproject.com> wrote:
> I want to insert the following data into a database.
>
> <xml>
> <value1>56</value1>
> <value2>1222</value2>
> <value3>78.5</value3>
> </xml>
>
> This is easy and the SQL command would look like this:
> INSERT INTO readings(value1, value2, value3) VALUES("56", "1222",
> "78.5")
>
> However, if the data looks like this what is your suggestion on database
> design?
> <xml>
> <value1>56</value1>
> <value2>1222</value2>
> <value2>LOOK</value2>
> <value3>78.5</value3>
> </xml>
>
> I want to put all data in the same record, but how should I manage the
> duplication of <value2> in a nice way?
One option would be to use a database that support array/list/enumeration
fields. If the values for 'value2' are one of a possible set, you can use the
ENUM() in MySQL or check(ordertype in(...)) in PostgresSQL
If the values for that field cannot be represented as an enumeration, then you
can use the array constructs in PostgesSQL
(http://www.postgresql.org/idocs/index.php?arrays.html).
Alternatively (if you want to be a little more portable) use a fixed format for
your field contents, e.g. put the values separated by commas. Another option
would be to use serialize() an array of the values into the field (OK if you
are going to use only PHP to serialize()/unserialize(), although there is also
Perl code that can read that), or better yet,
wddx_serialize()/wddx_unserialize().
Of course when it comes to searching on data in such a field, then things can
be tricky. Using the db-dependent solutions will cause less pain. Making you
own list/array will involve some contortions in your SQL, and using serialized
data will make your SQL very hairy ;-)
A final option, in case the repeats are the norm and not the exception, would
be to further normalize your db, assign a unique id to the record, create
another table where you store the multiple records corresponding to the
multiple values of the field, along w/ the appropriate id. This will make you
need to do one more join, but your SQL will not be crazy ;-), and the schema
should be portable.
> Best regards Fredrik Davidsson
HTH
=====
--- Jesus M. Castagnetto (jcastagnetto@yahoo.com)
Research:
http://metallo.scripps.edu/
Personal: http://www.castagnetto.org/
__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
------------------------ Yahoo! Groups Sponsor ---------------------~-->
Turn flat surfaces into speakers with the Soundbug.
http://us.click.yahoo.com/QWAVSC/onCFAA/sXBHAA/saFolB/TM
---------------------------------------------------------------------~->
Look here for Free PHP Classes of objects:
http://phpclasses.UpperDesign.com/
To unsubscribe from this group, send an email to:
php-objects-unsubscribe@egroups.com
Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
