0

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
Improve this question
5
  • if you store menu & date-time in other table, it help you at the time search & also normalized table always help the processing speed. But whether you need it or not, will depends on your requirement.whether there is any option to search menu name, then 2nd table is effective. to save data in your date table will depend on your business logic Commented May 5, 2016 at 7:20
  • Thanks @DipanwitaKundu but How can I update value if I use another 2 table ? To update value, should I need to delete existing all value of specific ID and save all data again ? Commented May 5, 2016 at 7:29
  • Let me know why date table store duplicate date3_time.... Commented May 5, 2016 at 7:31
  • In my form User can I add multiple date and time. So If I use another table for date and time there will multiple date and time for 1 PID, right ? Commented May 5, 2016 at 7:33
  • then date table should contain user id also, so at the time of updataion, you will update those date where user_id=<your user_id> and product_id=<your_product_id> Commented May 5, 2016 at 7:35

1 Answer 1

Reset to default
0

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

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I understand your update query but you see that I have same 2 pid in date time table. Then how can I update it ? I think I have to update it based on table id. Thanks for your answer.
Yes you will, but that won't be a problem, because when displaying the results you will select the ID of the joined table so it will already be available to you when you SELECT the data and display it
SELECT products_time.* FROM products_time JOIN products ON products.id = products_time.product.id

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.