Timeline - database setup

[emehrkay]

So I am creating an application that will take various inputs and add them to a timeline. So if i have an event and i mark it as Dec 28 2006, etc., it will show up when i run the timeline query.

now my question is how should i handle this and keep it as dynamic as possible - so that people could give ranges or specific dates, exclude dates etc.?

my first idea was to create a basic time table where time_id is the primary key and the other field would be the actual date (DATE). i would start from lets say 1900 and go to 2010(for now) with everyday being an entry.

time_id time_date
1  1900-01-01
2  1900-01-02
...
n  2010-12-31

i wanted to do it this way so that when you give something a date on the client side, the server and mysql would just input an time_id or if someone gave a range it would just be a range of time_id's and not actual dates that have to be processed by the server.

does this make sense, or should i approach it a different way? how many records can a single mysql table hold?

thanks

[paul2463]

having limited knowledge of mysql databases, i would not do what you are doing with the extra table to store each day and  as such assign an id value to it and use the id values in other tables, not all the dates are going to be used ( on average not all anyway) so you would be better off storing the selected dates in the main tables and use the BETWEEN sql function to get the range of dates you wish to use such as
[code]
<?php
$query = "SELECT allevents FROM eventtable WHERE eventdate BETWEEN 2001-01-02 AND 2004-01-02";
?>
[/code]


me of little knowledge has now stopped typing

[emehrkay]

paul i thought about that approach, but with a lot of sources with different date ranges, i figured it would be better to have the one table with everydate represented by an unique id.

im just wondering if there are any drawbacks to my suggested approach

[448191]

I don't see any benefit to redefining dates. I recommend using unix timestamps.

Using a translation table will likely only increase processing rather than reducing it.