shibbir ahmed shibbir ahmed - 7 months ago 16
SQL Question

Should I use multiple mysql database table to store php array data?

I have a Food Website where chef can add their product details from a form.

Form field name is bellow :


  1. name

  2. description

  3. price

  4. list of menu ( user can add more )

    a. menu name

    b. menu description

    ( Add more + )

  5. Data and time ( User can add more)

    ( Add more + )



According to this form I am using following table structure to mysql database :

Table name : products

pid name des price menuname menudes datetime
--------------------------------------------------------------------------

1 chicken des 3 serialize data serialize data serialize data
2 chicken2 des 4 serialize data serialize data serialize data
3 chicken3 des 5 serialize data serialize data serialize data
4 chicken4 des 6 serialize data serialize data serialize data


So, you see that
list of menu
and
data and time
fields can be 3, 4, or more. So that, I am using php array to get value from these 2 fields and serialize it and then saving it to database.

My Question is :

1) Should I use another 2 table for that
list of menu
and
data and time
field ? If so, why ?

2) Or, I should use php serialize data to store these 2 fields value like currently what I am doing ?

3) If I used another 2 table to store these 2 fields data How can I update value ?

For example : Below table, You see that
pid
1 has 2 date time value. So to update any one value out of 2, Should I need to delete these 2 date time and save the date time again ?

date table :

id pid dat3_time
----------------------
1 1 1201245772
2 1 1201245772
3 2 1201245772
4 2 1201245772
5 3 1201245772

Answer

1.- Yes you should, for scalability reason and to keep you structure consistent you should store all products related to a menu

2.- It is a posibility, but you won't be able to search by products and you will have trouble updating or removing elements asociated with the menu.

3.- That's the basics of SQL, I suggest you to read about joins and updates. One simple and not working example would be.

UPDATE products_time SET available_time = $newtime 
JOIN products ON products.id = products_time.productId
WHERE product.id = $productId