Best way to store a lot of dates to the Datebase?

The best and the easiest way to store and return data with long array of info always will be JSON. It's easy to convert to an object and fast.

The question here is, you will need to do searches trough this data? if the answer is affirmative, you will have to store data by raws instead fields in JSON format.

I believe I will need to do searches through it. If a user wants to see all the events on a specific date, I need to search through all the dates and display the ones that correspond. So you suggest raw? Should I just go look up saving to a database as raw or do you know any good tutorials?

It's not the same get the events of a particular date than make a search though all events.

In the first case, you can have a Events table like that:

Events Table

|id |date |events -> raw data in JSON |created_at |updated_at

So if you want all events of a particular date you could do:

Event::where('date','DD/MM/YYYY')->get();

In the opposite, if you also want to do things like that:

Event::where('events','foo')->get();

You will need to use separate rows per event.

Update

If you need to use the second option, event per row, consider use indexes to improve database performance when you make searches

Maybe a related table with the event_id as fk and the date, set up that way you can query it.

@jlrdw So a database that has each date as a column and then I do a pivot table to connect each event to that date?

Yes I would just treat that like any other parent-child relation.

Now the pivot table I don't really use but I know you do.

But that was just my idea.

You mentioned wanting to search that way it would not inhibit any search.

Of course you may need more fields.

But again I was just thinking about ideas.

It is a very interesting way to look at it. I will do some more research into it!

Would a pivot table linking a date to multiple events be slower or faster than an event having an array of Json dates?

@chrisgrim For a smaller amount of data such as events, the difference wouldn't be great. I was thinking of the "search" stand point.

Me, I would have another DB to simply backup events after a certain time. That way they are archived, but not needed in current DB.

At the trucking co I worked at we would purge data into an archived DB.

Was no need to have truck load data from 2003 in the 2007 database.

But DO NOT delete, have to save an archive for quite a few years, just to be safe.

I will definitely have a backup Database, that is a great idea. I am going to keep looking into the best way to save dates. I haven't seen any explicit answer so far.