Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2005
    Posts
    4

    Thoughts on schema

    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

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by bdensmore
    The Application is designed to hold a Title,Description, a list of dates and a list of times associated with each date.
    About what? is this a pet project? is it homework?

    Quote Originally Posted by bdensmore
    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
    Very poor idea - look up the datetime data type.

    Quote Originally Posted by bdensmore
    Does anyone have a better idea that might scale well and not bloat the database too much?
    how many billions of records are you planning on storing?

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    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.

  4. #4
    Join Date
    Dec 2005
    Posts
    4
    @mike_bike_kite
    Very poor idea - look up the datetime data type.
    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.

    how many billions of records are you planning on storing?
    I'm not sure how much traffic this site currently gets, so I really don't know how many records will be created.

    @dportas

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

  5. #5
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by bdensmore
    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.

  6. #6
    Join Date
    Dec 2005
    Posts
    4
    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

  7. #7
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    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.

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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:
    Code:
    Title Table
    titleID|userID|title|description
    
    Date Table:
    titleFK|datetime_combined|...
    The following uses MySQL syntax to show choice of dates and then choice of times on that date:

    Code:
    -- dates
    select distinct date(datetime_combined) from DateTable;
    
    -- times on a date
    select distinct time(datetime_combined) from DateTable
    where date(datetime_combined) = '2009-04-25';
    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.

  9. #9
    Join Date
    May 2009
    Location
    India
    Posts
    66
    Purely by normalising, it should be:

    Title Table
    titleID|userID|title|description

    Date Table:
    titleFK|dateID|datevalue

    Time Table:
    titleFK|dateFK|timevalue

  10. #10
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by AnanthaP
    Purely by normalising, it should be: ...
    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.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ya know AnanthaP ain't the OP yah Mike?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by pootle flump
    Ya know AnanthaP ain't the OP yah Mike?
    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
    .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •