I have an application that I'm working on and have now moved on to designing my data model.

The Application is designed to hold a Title,Description, a list of dates and a list of times associated with each date.

Because users can add as many dates and times as they want I am thinking of having a table that creates a new row for each date added and then a separate table for times which will insert a new row for each time associated to the date.

So in my head I am thinking of something like this:

Title Table
titleID|userID|title|description

Date Table:
titleFK|dateID|datevalue

Time Table:
dateFK|timevalue

This will allow a user to enter in an infinite amount of dates and times. My only concern might be the amount of records that will be contained in the DB.

Does anyone have a better idea that might scale well and not bloat the database too much?

Thanks,
Ben

About what? is this a pet project? is it homework?

Very poor idea - look up the datetime data type.

how many billions of records are you planning on storing?

In your model there is no way to relate dates and times to the users. I don't see why you'd put dates and times in separate tables.

@mike_bike_kite
I know about the datetime data type. The data will be stored as correct data types, but because a user can store as many dates as they want I won't have a way of knowing how many columns are needed to hold the dates for a given user.

I'm not sure how much traffic this site currently gets, so I really don't know how many records will be created.

@dportas

The Title table holds the userID key, from there I would join on the other 2 tables based on the titleID and DateID.

How would you store the dates and times if a user adds 5 dates, and each date has 5 times associated with it. This is a scheduling application so that users can add a bunch of dates and add times to those dates selected.

Is it the case that every time can apply to every date for a given user? If so, then you may save a lot of space by creating two tables. If not, then I'd use one table containing both date and time - probably in a single column assuming your DBMS supports that.

each user will create a schedule. The schedule will contain either one date or 20 dates. each one of those dates will have multiple times associated with it. But as I said, I won't know how many of each they will add which is why in my head I was thinking a one to many relationship between dates and times. Each date can have many times so if they enter 3 dates and each date has 5 times it would be like:

titleID dateid dateval
1 1 2009-06-01
2 2 2009-06-02
3 3 2009-06-03


datefk timeval
1 9:00am
1 10:00am
1 11:00am
1 12:00pm
1 1:00pm
2 5:00pm
2 6:00pm
2 7:00pm
2 8:00pm
3 5:00pm
3 10:00pm


Does that make a little more sense? Another thought I had was to do like a tree where I store the dates and times in one table and have the date contain a top level id and the times for that date relate to that.

id parentid level val
1 0 1 2009-06-01
2 1 2 9:00am
3 1 3 10:00am

Ben

I would put date and time in one table. I don't see any reason to use two tables for it. You might still want to use separate columns for date and time depending on how they are used.

I would store the date and times in one field. You can still present the data to the user as first a list of dates and then a list of times for that date using this simple schema. Tables would be:
The following uses MySQL syntax to show choice of dates and then choice of times on that date:

If you're going to store the data in 2 fields then try the following experiment - show all the records where the combined date and time are between 9am on one day and 9am the next. Then try it with a single datetime field and see how easy it is.

Purely by normalising, it should be:

Title Table
titleID|userID|title|description

Date Table:
titleFK|dateID|datevalue

Time Table:
titleFK|dateFK|timevalue

You originally asked if anyone had any better ideas and I believe we gave them to you. Using the information you have given to us I stand by what I've said but obviously you know your data better. You could try glancing at this thread which came in yesterday which is about problems occurring from having separate date and time fields.

Out of curiosity would you want to split the date field into a year field, a month field and a day field and then perhaps store all these fields in different tables? you could go further and perhaps store hour, minute and second in different tables to.

Ya know AnanthaP ain't the OP yah Mike?

pootle flump ur codings are working excelent.

Oh bugger - where did he come from ?

I suppose he should just read from the bit that starts "You could try glancing at" and ignore the first bit then
.