Normalisation Nightmare

[HeavenCore]

Hi all, Deep Breath

i have a complex database and one of the factors is a 'jobs' table. However when i start to break the data down i am getting lost. I have attempted entity relationship diagramming but ive hit a wall.

1 Job has many Enclosures
1 Enclosure can have many weeks of work
a week of work table is as follows:

-----------Day1 - Day2 - Day3 - Day4 - Day5 - Day6 - Day7
DECON
PreClean
Task3
Task5


as you can see each week has both X and Y axis labels, how would i go about storing this in the database? The axis will always be the same there is 8 tasks(y-axis) and 7 days obviously (x-axis)

i was thinking an Enclosures table and an EnclosureWeeks table with a 3rd table to cope with the many to many relationship. But the structure of the EnclusureWeeks table is proving tricky (especially when coming to databinding structure etc?)

Can anybody help?

 

[Arg81]

Is any of the week of work static? or does the data dynamically change?

 

[HeavenCore]

the data can be changed yes, and searchable, i was thinking an events table with day week, and time data :S suffice to say databinding is going out the window for this one lol.

 

[Arg81]

I thought if it was static you could do something like this....

DayWork

DayWorkID -- Day -- Decon -- Preclean -- Task3 -- Task4 -- Task5

1------------Mon---- 1---------0 ----------1--------0--------0
2------------Tues----0---------1-----------1--------1--------1
3------------Wed-----1---------1----------0---------0--------1
4------------Thurs-----0-------0------------0--------0--------1
5------------Fri--------1-------0------------1--------1--------1

So each task would be a Boolean column set to either true or false.


EDIT (I wrote the following line wrong)

You could then say 1 Enclosure has many Weeks of Work which has many DayWorkID's. So the above table could have 4 different Mondays in it, each with the values changed accordingly to what is done on the 4 Mondays.

You then have a WeeksWork table which looks like

WeekWorkID-----DayWorkID

1-----------------1
1-----------------2
1-----------------3
1-----------------4
1-----------------5
2-----------------6 ---------------Monday-----1-----0----1----0-----1 (another Monday row in your DaysWorkID table)


Think that edit makes sense :S

 

[HeavenCore]

hi m8, thanks for your reply!

i have already kinda came up with a solution, too hard to explain, but find attached a rough ERD. Basically i work on the principle of the user adds tasks to a week etc, it breaks down to 5 tables , although two are to cope with m2m relationships. But this way i will have to manually output some kind of grid using the data but it will also allow to fully search tasks by date, job, week, etc. Queries are gonna be a bitch but i think it should work

 

[cjard]

as you can see each week has both X and Y axis labels, how would i go about storing this in the database? The axis will always be the same there is 8 tasks(y-axis) and 7 days obviously (x-axis)

Not by making tables rectangular; they dont work that way. Tables are fixed width, variable length, and the variance allows multiple entities to be stored.

You'd fix the columns as the tasks, and have the dates as the values within the columns, or if a task can be done more than once/takes more than one day then you would have a dictionary type table:

WeekID, Date, Task

Reporting tools would pivot this data into a crosstab if needed