1

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

CodeGodie
  • 12,116
  • 6
  • 37
  • 66
aahhaa
  • 2,240
  • 3
  • 19
  • 30
  • I think that question is opinion-based, there is a lot of answers and none of them will be necessarily the most correct one. Anyway it's up to the developer in charge of handling the fetched data to decide how to store it, since it'll be the person who will create the code flow, the inputs and the outputs. Whatever makes your work easier and the usability viable. – al'ein Sep 21 '15 at 16:46
  • 1
    Here is my opinion, as seen [here](http://stackoverflow.com/a/32620163). Create a Junction Table – Drew Sep 21 '15 at 16:48
  • @Drew I read your answer, thanks, but there is unlimited amount of date, will that be a issue, sorry i am very noob at this. – aahhaa Sep 21 '15 at 16:52
  • 1
    there is a nuance difference in most questions. Point being, arrays and CSV jammed in a column is not the way to go. – Drew Sep 21 '15 at 16:55
  • You should store it as a json. I never used but you can try. – Elias Nicolas Sep 21 '15 at 18:04

2 Answers2

2

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."

CodeGodie
  • 12,116
  • 6
  • 37
  • 66
  • funny thing is, order_dates table probably isn't needed for this question. The order simply has the start_date and end_date in it :) I remember a question a while back where clearly the guy's friend said "keep your strings in another table". He had many tables just for the user table – Drew Sep 21 '15 at 17:06
  • @Drew so how do you expect to store multiple start/end dates per order if not with another table? – CodeGodie Sep 21 '15 at 17:11
  • I will have to slowly re-read the question. Edit: I see, I see, said the blind man. Good thing I said "probably", gives me a slimy retreat – Drew Sep 21 '15 at 17:12
  • No problem, Just thought you had a better way of doing it. I hate it when I oversee things as well. – CodeGodie Sep 21 '15 at 17:20
  • 1
    exactly. I was having a senior moment, my apologies – Drew Sep 21 '15 at 17:21
0

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

Danish Ahmed
  • 171
  • 1
  • 9
  • 1
    will probably work but you should not be storing arrays, specially dates, in a database. You want to be able to query the database in the future using SQL functions and indexes. – CodeGodie Sep 21 '15 at 17:02
  • 2
    yes exactly, i completely agree with you :) my approach is not good. it's just a way to store an array if a person is restricted or bounded by an array. It's just a general solution. Once again I second you :) – Danish Ahmed Sep 21 '15 at 17:05
  • 2
    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. – Oliver Maksimovic Sep 21 '15 at 17:16
  • yes it is best when you need to do computation on server side and when you have data in form of dynamic array. But retrieving from it is not useful as you cannot apply operations to it. – Danish Ahmed Sep 21 '15 at 17:19
  • write that up as an Answer if you want @Oliver. It is a valid/great point. I would happily reference it in my current marketing drive ! – Drew Sep 21 '15 at 17:22
  • 1
    Drew well, @CodeGodie's answer is actually summing it all up, especially because one of OP's examples clearly indicated that multiple dates/ranges would be required. He can update the answer about (not) using serialized data if he feels like improving it, and you can link to my comment anyway ;) – Oliver Maksimovic Sep 21 '15 at 17:35
  • Thanks @Oliver, I will edit my answer to include your comment and link it to you. – CodeGodie Sep 21 '15 at 17:39
  • I edited my answer.. thnx oliver for your contribution :) – Danish Ahmed Sep 21 '15 at 17:50