Link to home
Create AccountLog in
Avatar of DoveTails
DoveTailsFlag for United States of America

asked on

Database Design Question, correct junction table format

Hello, looking for advice on database design.
(examples below are simplified)

I'm building a MS Access database to track employees monthly work days assigned to a specific project. Eventually there will be more than 100 projects with about 35 employees.

Currently a spreadsheet is used with Projects as the row header and employee names by month as column headers.

Example:
------------------------------------------------------------------------------------------------
PROJECT                      JANUARY Days                      FEBRUARY Days
                            Bob         Jane         Sue               Bob       Jane       Sue                        
------------------------------------------------------------------------------------------------
Road Work             2              0               3      |         0            20            7
Field Data              0              4               9      |       12              0            4
Geothermal           4               6              7      |         9              3            1
------------------------------------------------------------------------------------------------

I've created an Employee table, a Projects table, a Months table and a Junction table for the many to many relationship.

I've designed the junction table 'VERTICALLY' ... what I mean is, I have a column for Project, Month and  for AssignedDaysEachMonth (without Months running horizontally):
Example:

Employee     Project      Month      DaysAssinged
-----------------------------------------------
Bob      Road Work                 Jan           2
Bob      Road Work                 Feb           0
Bob      Field Data                   Jan           0
Bob      Field Data                        Feb         12
Bob      Geothermal...etc

This design allows me to create a Pivot Table that mimics the original spreadsheet (which the managers like to see), but a Pivot table cannot be edited.  So I'm thinking in order to edit the data, a user will have to open a form for a specific Project (and see all the employees days to edit) or a specific Employee form (and see all the projects that can be assigned) ... instead of being able to see everything like the pivot table and simply click in a cell to edit.  

I don't know if I should keep the junction table the way it is or redesign it. (maybe have a column for January days, one for Feb days, etc. ie more HORIZONTALLY, though this seems like a poor design). Maybe it's not possible to edit the data in a spreadsheet type format (?).

So my question is, can you offer me advice on how to best proceed with the database design?  To me, this really means how the Junction table is built, but I'm open to any help to begin this work so the data can be easily edited over time and also be able to be viewed in a spreadsheet type format.

Appreciate any feedback ...  Thank you.
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of DoveTails

ASKER

Thank you fyed and boag2000.
I like the date idea and appreciate the feedback on the table structure.
As for MS Project, glad you mentioned that ... I brought that up during our first meeting to discuss the database. They had used MS Project before on one or a few projects at a time, but in this case (with so many projects) they're looking for a different solution.
Both of your comments give me some ideas and make me feel I'm on the right path.
Thanks