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:(

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.

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

hi all,

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

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.

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

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?:p

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

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