Results 1 to 6 of 6

Thread: databasedesign?

  1. #1
    bea Guest

    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

  2. #2
    al Guest

    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

  3. #3
    bea Guest

    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

  4. #4
    Lind Guest

    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

  5. #5
    al Guest

    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

  6. #6
    Bea Guest

    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

Posting Permissions

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