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

Improve this question
5
  • 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. Commented Sep 21, 2015 at 16:46
  • 1
    Here is my opinion, as seen here. Create a Junction Table Commented Sep 21, 2015 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. Commented Sep 21, 2015 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. Commented Sep 21, 2015 at 16:55
  • You should store it as a json. I never used but you can try. Commented Sep 21, 2015 at 18:04

2 Answers 2

Reset to default
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."

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

5 Comments

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 so how do you expect to store multiple start/end dates per order if not with another table?
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
No problem, Just thought you had a better way of doing it. I hate it when I oversee things as well.
exactly. I was having a senior moment, my apologies
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

Improve this answer

8 Comments

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.
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 :)
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.
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.
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 ;)
|

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.