-
databasedesign?
Hello!
Im trying to make an application where you are supposed to fill in workhours every day and a comments
column and another column. I use week to get the different days; week, mon, tues,wén..comments, anothercolumn.
I link it to a usertable. this means that for every week every employee gets 7 rows where 6 weekdays are null and one contains the workhours for the actual day. Is this good or bad design? Is there another way to build this
I would be very gratefull if anyone comment on this
/bea
-
databasedesign? (reply)
You are trying to say that you have a table like this every day:
Day Hours Comments Other COlumn
-------------------------------
MON null null null
TUE null null null
WED null null null
THU 8 XXXX XXXX
FRI null null null
SAT null null null
SUN null null null
And every day you change the values, according with the current day you are reading the database?
------------
bea at 4/17/01 9:55:08 AM
------------
bea at 4/17/01 5:08:10 AM
Hello!
Me again! I forgott to say that everyday you fill in the comment and the other column, so when I do a select I get 6days nullvalue one day 8 h + comment and the other column, this means that i use the two nondaycolums 7times/week. Will this work?
/bea
-
databasedesign? (reply)
yes,the comment and the other column are filled in everyday its just the weekdays that are blank, if you fill in comments and other column everyday there will be so many nullvalues. This is for one employee one week;
Week Mon tue wen thu fri sat sun Comments Other COlumn
----------------------------------------------------------
1 8 null null null null null null xxxxxxx xxxxxxxx
1 null 8 null null null null null xxxxxxx xxxxxxxx
1 null null 8 null null null null xxxxxxx xxxxxxxx
1 null null null 8 null null null xxxxxxx xxxxxxxx
1 null null null null 8 null null xxxxxxx xxxxxxxx
1 null null null null null 8 null xxxxxxx xxxxxxxx
1 null null null null null null 8 xxxxxxx xxxxxxxx
/bea
------------
al at 4/17/01 4:01:36 PM
You are trying to say that you have a table like this every day:
Day Hours Comments Other COlumn
-------------------------------
MON null null null
TUE null null null
WED null null null
THU 8 XXXX XXXX
FRI null null null
SAT null null null
SUN null null null
And every day you change the values, according with the current day you are reading the database?
------------
bea at 4/17/01 9:55:08 AM
------------
bea at 4/17/01 5:08:10 AM
Hello!
Me again! I forgott to say that everyday you fill in the comment and the other column, so when I do a select I get 6days nullvalue one day 8 h + comment and the other column, this means that i use the two nondaycolums 7times/week. Will this work?
/bea
-
databasedesign? (reply)
How about eliminating fields Mon, Tue, etc and replace them with a field called Day. The day of the week is entered in this column. Then, to total by day of the week, use a crosstab query.
------------
bea at 4/17/01 5:08:10 AM
Hello!
Im trying to make an application where you are supposed to fill in workhours every day and a comments
column and another column. I use week to get the different days; week, mon, tues,wén..comments, anothercolumn.
I link it to a usertable. this means that for every week every employee gets 7 rows where 6 weekdays are null and one contains the workhours for the actual day. Is this good or bad design? Is there another way to build this
I would be very gratefull if anyone comment on this
/bea
-
databasedesign? (reply)
Why do not you use this design on that table:
<pre>
Week Day hours Comments Other_column
1 1 8 xxxxxxx xxxxxxxxxxxx
1 2 8 xxxxxxx xxxxxxxxxxxx
......
2 1 8 xxxxxxx xxxxxxxxxxxx
2 2 8 xxxxxxx xxxxxxxxxxxx
2 3 0 xxxxxxx xxxxxxxxxxxxx
.....
</pre>
If you want to relate this to a employee , so you can just add a employee id column. This design can reduce table size and your SQL queries could be easier than having all 7 columns with the days. There is no use to have columns with NULL values.
Hope this helps.
------------
bea at 4/18/01 4:00:34 AM
yes,the comment and the other column are filled in everyday its just the weekdays that are blank, if you fill in comments and other column everyday there will be so many nullvalues. This is for one employee one week;
Week Mon tue wen thu fri sat sun Comments Other COlumn
----------------------------------------------------------
1 8 null null null null null null xxxxxxx xxxxxxxx
1 null 8 null null null null null xxxxxxx xxxxxxxx
1 null null 8 null null null null xxxxxxx xxxxxxxx
1 null null null 8 null null null xxxxxxx xxxxxxxx
1 null null null null 8 null null xxxxxxx xxxxxxxx
1 null null null null null 8 null xxxxxxx xxxxxxxx
1 null null null null null null 8 xxxxxxx xxxxxxxx
/bea
------------
al at 4/17/01 4:01:36 PM
You are trying to say that you have a table like this every day:
Day Hours Comments Other COlumn
-------------------------------
MON null null null
TUE null null null
WED null null null
THU 8 XXXX XXXX
FRI null null null
SAT null null null
SUN null null null
And every day you change the values, according with the current day you are reading the database?
------------
bea at 4/17/01 9:55:08 AM
------------
bea at 4/17/01 5:08:10 AM
Hello!
Me again! I forgott to say that everyday you fill in the comment and the other column, so when I do a select I get 6days nullvalue one day 8 h + comment and the other column, this means that i use the two nondaycolums 7times/week. Will this work?
/bea
-
databasedesign? (reply)
Thank you for helping me out!/Bea
------------
al at 4/19/01 10:22:12 AM
Why do not you use this design on that table:
<pre>
Week Day hours Comments Other_column
1 1 8 xxxxxxx xxxxxxxxxxxx
1 2 8 xxxxxxx xxxxxxxxxxxx
......
2 1 8 xxxxxxx xxxxxxxxxxxx
2 2 8 xxxxxxx xxxxxxxxxxxx
2 3 0 xxxxxxx xxxxxxxxxxxxx
.....
</pre>
If you want to relate this to a employee , so you can just add a employee id column. This design can reduce table size and your SQL queries could be easier than having all 7 columns with the days. There is no use to have columns with NULL values.
Hope this helps.
------------
bea at 4/18/01 4:00:34 AM
yes,the comment and the other column are filled in everyday its just the weekdays that are blank, if you fill in comments and other column everyday there will be so many nullvalues. This is for one employee one week;
Week Mon tue wen thu fri sat sun Comments Other COlumn
----------------------------------------------------------
1 8 null null null null null null xxxxxxx xxxxxxxx
1 null 8 null null null null null xxxxxxx xxxxxxxx
1 null null 8 null null null null xxxxxxx xxxxxxxx
1 null null null 8 null null null xxxxxxx xxxxxxxx
1 null null null null 8 null null xxxxxxx xxxxxxxx
1 null null null null null 8 null xxxxxxx xxxxxxxx
1 null null null null null null 8 xxxxxxx xxxxxxxx
/bea
------------
al at 4/17/01 4:01:36 PM
You are trying to say that you have a table like this every day:
Day Hours Comments Other COlumn
-------------------------------
MON null null null
TUE null null null
WED null null null
THU 8 XXXX XXXX
FRI null null null
SAT null null null
SUN null null null
And every day you change the values, according with the current day you are reading the database?
------------
bea at 4/17/01 9:55:08 AM
------------
bea at 4/17/01 5:08:10 AM
Hello!
Me again! I forgott to say that everyday you fill in the comment and the other column, so when I do a select I get 6days nullvalue one day 8 h + comment and the other column, this means that i use the two nondaycolums 7times/week. Will this work?
/bea
