CODEWITHSUNDEEP
phpmysql
aahhaa
aahhaa

Reputation: 2275

Alternative to store array in mySQL database. Multiple dates per order

I am building a reservation system, that can store dates of reservation, I am concerning how to store that data.

I want to have a table as such 

order_id     DateRSVP   
----------   ------------ 
1            1/1-1/3, 1/5, 1/7

should I store it as string and implode explode on , 

order_id     DateRSVP   
----------   ------------ 
1            1/1, 1/2, 1/3, 1/5, 1/7

or should i do 

order_id     DateRSVP
----------   ------------
1            1/1
----------   ------------
order_id     DateRSVP
----------   ------------
1            1/2
----------   ------------
order_id     DateRSVP
----------   ------------
1            1/3

or is there a more efficiency way to accomplish it

Upvotes: 1

Views: 838

Answers (2)

Danish Ahmed
Danish Ahmed

Reputation: 171

Edited:
Serializing is not a best approach and should not be used when trying to store date in database. Storing anything to database in serialized form is fine as far as you DO NOT need to apply operations on it, such as: order by, like (for search), where clauses etc. It can be specially used when your data is in form of dynamic array, or where you need not to create a dynamic table.
Thnx Oliver for contributing :)
---------------------------

if you have an array and you want to store it in database then you can serialize your array and then store it in database
$store = serialize($your_array);

after retrieving it from database; you can get you array back by:
$result_array = unserialize($sql_value);

for more reference:
serialize
unserialize

Upvotes: 0

CodeGodie
CodeGodie

Reputation: 12132

I would create two tables. Orders and Order_dates. Orders will just list the order info including the order_id. Order_dates would have the dates stored using the DATE data type like so:

+----------+------------+------------+
| order_id | start_date | end_date   |
+----------+------------+------------+
| 1        | 2016-01-01 | 2016-01-03 |
+----------+------------+------------+
| 1        | 2016-01-05 | 2016-01-05 |
+----------+------------+------------+
| 1        | 2016-01-07 | 2016-01-07 |
+----------+------------+------------+
| 2        | 2016-02-07 | 2016-01-07 |
+----------+------------+------------+

that way you can easily query it the right way if needed in the future using SQL functions and indexing. As @Oliver said in one of his comments: "Storing serialized data is okay if you don't need to search through it, especially if it would require lots of columns or even a dedicated table. However, if one ever has the need to use it for search, order, group by... then don't even think about serializing such data."

Upvotes: 2

Related Questions