Page 1 of 1

What is the best way to do this?

Posted: Wed Sep 03, 2008 7:34 pm
by zunebuggy
I am creating a MySQL database. I am having trouble with one of the fields. I am not sure of the best way to do this. People can add information to the database but on one field they can enter multiple items. For example lastname = Smith, firstname = John, object_list = apples, oranges, bananas, grapes... This field could contain one item or more. When the data is transferred to my database, I would either like it to create multiple records like: Smith, John, apples | Smith, John, oranges | Smith, John, bananas, ec... OR leave the field as a multiline text field but when someone searches for Smith AND John AND apples, it would only return Smith, John, apples and not all the other objects. I could ask people to add the objects and separate them with commas but this doesn't guarantee they will and I don't think this is the best way. What is the best way to do this? Thank you

Re: What is the best way to do this?

Posted: Wed Sep 03, 2008 10:33 pm
by deeessay
You have to create another table for that.

Okay, so you have a table containing people with fields
lastname, firstname, and include a people_id field which is unique and the primary key.

Now create another table named object_list
now add the people_id field, obj1, obj2, obj3, obj4, etc etc

for example there is an entry in the people table

with a people_id='usr1', lastname='doe', firstname='john'

in the objects_list table entry will be
people_id='usr1', obj1='apple', obj2='grapes', obj3='orange', obj4='NULL', obj5='NULL', etc etc,,,,

Re: What is the best way to do this?

Posted: Wed Sep 03, 2008 11:16 pm
by andyhoneycutt
if it has to be a single field in the same table, i would suggest using a class and serializing it and storing that in the database. although, if you have a pattern for each possible entry and you can get away with having it in a separate table, deeessay has the right answer: use a table with a column for the person, the object, and it's description, and have each item be a row in that table. Personally, I would take deeessay's approach.