architecture question

Posted on 2009-12-21
Last Modified: 2012-05-08
I have a request to build a one page time reporting form that allows a user to select any Sunday of the year.  Once they have selected a Sunday, they should be shown a table with 7 columns representing the days of their selected week; each column should have an input to allow the user to enter the number of hours they worked that day.  Upon save the user records are stored in the database.

I already have a a user table, but I need to create a table for the time reporting.  I should have one table for the days of the week....i.e. Sunday thru Saturday with a foreign key relationship to the user, but I'm a little thrown by the any Sunday of the year selection?  How should that be handled?  How should I capture that in the database (another table)?  Also, how would I allow the user to only select a Sunday out of the year?  I can have a calendar popup, but I'm not quite sure how to only allow the Sundays of the year be selected.

Any suggestions or better ideas on the table construction would be helpful?

Question by:-Dman100-
    LVL 51

    Expert Comment

    I use datetime

    userid: varchar, workday: date, hours:double

    or use this

    userid: varchar, weekno: int, day1, day2, day3, day4, day5, day6, day7: double

    think which one is better for your app
    LVL 51

    Expert Comment

    oops, we need to add a year to second option as

    userid: varchar, year:int, weekno: int, day1, day2, day3, day4, day5, day6, day7: double
    index: userid, year, weekno

    for first option
    index: userid, workday
    LVL 51

    Accepted Solution

    if you will work with weeks all the time, 2nd option is better... 1/7 of records will be in db compared to 1st opt

    if you work with individual day then 1st option is better...

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now