Results 1 to 8 of 8

Thread: please help with this table design

  1. #1

    Thread Starter
    PowerPoster Nitesh's Avatar
    Join Date
    Mar 2007
    Location
    Death Valley
    Posts
    2,556

    please help with this table design

    hi guys,

    I need to define weeks in a table. So when a user wants to add a week he types is a description, selects the number of days for that week and then saves.

    i need part of my datagridview to look like this:

    so I select a week from a combobox as defined above, then the days defined for that week appear as columns, then I need to tick the days a person was present

    Code:
    Days Present
    Day1	Day2	Day3	Day4	Day5
    my question is:

    how do I define the tables to store the week and days information? do i need just one table? or do I need a parent/child scenario here.

    please please help. im really stuck

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: please help with this table design

    When designing tables you shouldn't focus on the way it will be displayed/used in the program - you should focus on what data needs to be stored.

    So far all we know is that each Day should be able to store a single value (perhaps a boolean), but we don't know what data should be stored for a Week.

  3. #3
    PowerPoster
    Join Date
    Nov 2002
    Location
    Manila
    Posts
    7,629

    Re: please help with this table design

    Since you mentioned parent/child please refer to this thread.... http://www.vbforums.com/showthread.php?t=591874

    Common error with table design is relating data on the assumption that ALL participants/records in the relationship exist in the database or arrive/encoded at the same time when in reality it is not so. There should be some flexibility in the design, separate tables for entities/reference data (e.g. calendar table so you can also tag holidays and clearly identify working days which can be used for automation of processes/calculations) and transactions (e.g. what the dates are for in relation to something else, e.g. attendance).

  4. #4

    Thread Starter
    PowerPoster Nitesh's Avatar
    Join Date
    Mar 2007
    Location
    Death Valley
    Posts
    2,556

    Re: please help with this table design

    hi all,

    sorry but it's been a while since I posted on this topic. been too busy on other projects

    thanks for all the input. im still quite confused. i need to define a week, maybe a description like "WeekNov1", then i need to assign valid days for that week. say I want to use Monday,Tuesday and Thursday.

    then when I select a week on my main form, I populate a datagridview and 3 checkbox columns. Day ,Day2,Day3. my confusion arises with this:

    if i have bit fields in my table to denote a yes or no, how can I link that bit field to a specific day. please help me experts.

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: please help with this table design

    So what data do you think you need to store? Ignore the linking between a Week and a Day for now, and just focus on the information you need for each of them.

    For a Week you mentioned 'maybe a description like "WeekNov1"', but is that really all you think you need? I would assume that you are likely to need a Date as well, and perhaps other info.

    For a Day you want to store a checkbox value, but based on the potential selection of days you presumably also need to store some kind of indicator of which day it is (maybe the date, or a number of days since the start of the week).

  6. #6

    Thread Starter
    PowerPoster Nitesh's Avatar
    Join Date
    Mar 2007
    Location
    Death Valley
    Posts
    2,556

    Re: please help with this table design

    thanks si. so far i can picture the week table having a weekID and description.

    my days table will have an ID, WeekID, Date(datetime datatype),and isPresent(bit datatype, to mark wheter they where present that day)

    am I on the right track?

  7. #7
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: please help with this table design

    That seems about right to me - I think it covers everything (including the link between the tables).

  8. #8

    Thread Starter
    PowerPoster Nitesh's Avatar
    Join Date
    Mar 2007
    Location
    Death Valley
    Posts
    2,556

    Re: please help with this table design

    thanks for all the valuable advice si. I appreciate the help

Posting Permissions

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



Click Here to Expand Forum to Full Width