I’d like to create a view that would hold data for every single workType for every single user for every single week, regardless of what data there is in the workAllocation table.
I would then use a query to get data out of this view for a given userId AND for a given weekId, creating a dataGrid, on which some row would contain data, some would be NULL.
I would then use another query to shift data back into the database on rows where there have been changes.
So if I had 2 week records, 2 user records and 2 work types in the database, I would have the following 8 lines in the view:
userA on week1 doing workTypeX
userA on week1 doing workTypeY
userA on week2 doing workTypeX
userA on week2 doing workTypeY
userB on week1 doing workTypeX
userB on week1 doing workTypeY
userB on week2 doing workTypeX
userB on week2 doing workTypeY
I’m note sure if I explained it correctly; on the other hand I’m sure some of you here will give me a much better design lead.
I’d much appreciate any thoughts on this, thanks in advance.