To normalise or nearly normalise? (1 Viewer)

[Cronk]

I'm about to embark on the development of a new system for the daily rostering of fire trucks.

Ordinarily, I'd be aiming at a Roster table along the lines of
RosterID
PersonID
TruckID
RosterDate
RoleID (as in driver, crew, foam specialist because certain members have different training)

Normally, the roster is being done by Depot. Staff and trucks are assigned to a Depot but staff from another Depot can be assigned to a truck at another Depot.

Rostering is done by week.

I will need to display in an editable form, the data in 2 formats (separate forms)

(1) Select a Depot and show all staff at that Depot and the trucks (or other tasks eg leave, office duties) they are assigned to, for the 7 day roster ie Staff as row headers, Roster dates as column headers.

(2) Select a truck and show the data in the same format but showing also staff that may be rostered on that truck for some days, but showing where they are assigned if otherwise. This display is for the purpose of ensuring that a full crew is rostered for a truck.

A report would be easy using a pivot query but it would not appear editable.

I'm inclined to make the roster table, not for one day but for the week ie having instead of RosterDate, RosterDate1, RosterDate2 ....RosterDate7

I recognise that this is not strictly normalised.

Alternatively, I could make the form unbound and load/save the form data OnCurrent from a purely normalized Roster table above.

In case anyone might wonder, there are reasons that there is only one shift per day.

Maybe the roster entity could be considered as having a week of 7 days (not scalable) and therefore is normalised ie instead of having the field RosterDate, it would be StartOfRosterWeek.

I'm very inclined to have 7 day fields but I'd welcome any comments before I commit.

 

[namliam]

The real question is do you need the data at some point to be split into days instead of into weeks...

Normalization surely is a custom build thing and is never the same, there is no rule that says you have to have day as a single entity, if you dont need it you dont need it.

There are even normal forms beyond the 3rd that purposely decompose the normalization process for different reasons.

 

[The_Doc_Man]

It is never wrong in theory to fully normalize your tables. Having said that, there is ALSO nothing wrong in practice if you have a reason to NOT fully normalize your tables.

A report would be easy using a pivot query but it would not appear editable.

Technically, it should NOT be editable unless you want to export it to another format and get it out of the database first. If you have to edit your report, you have suddenly found a case where normalization might have become an issue - or perhaps just a clunky design goal is in the way. (I can't tell which from here.)

We can never tell you that you should or should not fully normalize. We can, however, hope to offer a guideline or two.

If you choose to not fully normalize, you can gain some efficiency as long as you intended output formats never change. On the other hand, if your formats change a lot, you get burned if the unnormalized data needs extra massaging before you can generate the report you want.

If you choose to fully normalize, you have to sometimes work a bit to get the reports in the format you want but you will have all the data you need in a general format that is highly mutable.

There is also a strategy that says you would build a temporary table for your "calendar" and use queries or VBA to fill it, then just build a report directly from the temporary table. For temp tables, you don't give a rat's patootie about its format because you are going to wipe it clean when you are done with it anyway.

In the final analysis it will always reduce down to this question: What database format do you think you can reasonably manage to get the output you want from the inputs you have? If you can't answer this question, you aren't ready to do any detailed design work anyway.

 

[Cronk]

Both,

Thanks for the comments. It's very unlikely the week will ever be de-aggregated. (Famous last words!!) If for some future requirement, reports are to include odd days of a week, I can resort to exporting data to temporary tables.

I might do some speed testing on using temporary tables for inputting/editing data and then saving the changes back to daily normalised tables.

Again thanks for taking the time to respond.