Further normalisation

[Cait]

I have a table that tracks a series of (mostly) date-based events and I'm trying to decide whether to further normalise the table.

The process works like this:

1. We are notfied of a problem (PBTCID PK, PropertyID FK, DatePBTC)
2. We send a recorded letter (DateRecLet)
3. We send a 1st class letter (DateFirstLet)

If no response to letters then:

4. Problem assigned to a department (DateHousing)
AND
5. Problem assigned to specific person (HO - numeric, to lookup)

If no response after their involvement then:

6. Request possession order (DateReqNSP)
7. Possession order served (DateServNSP)

If no reponse after order is served then:

9. Go to court (DateCourt)
10. Record outcome of court proceedings (Outcome - numeric to lookup)

At present, all of the above is in one table. I would say that 70% of the time, the problem is resolved at step 3. A further 25% is resolved at step at 4/5. Another 4% at step step 7, with the remaining 1% going all the way to step 10.

Am I better off with one table, even though the majority of proplems won't go beyond step 3? If so, what would be the best way to break this out?

Any direction would be much appreciated.

 

[Rabbie]

Normalisation depends on your data structure so it would be very helpful if you could post your actual table structure so we can see in detail what could be improved.

 

[Cait]

Ok, this is the database. Above is slightly different than how the database is set out, but the process I describe above is the same and relates to tblPBTC and tblNoAccess.

All the personally and property identifiable information have been dropped because of the DPA, which is why you won't see property addresses in tblProperty.