Normalization Question

[Tom]

Hi:

I have a question regarding normalization of a database. I am trying
to model the inspection and repair of items. Also need to track when
they go in service, out of service and several other actions relating
to the item. My first shot has the following structure:

tblItem
· ItemID
· ItemName
· Etc

tblInspection
· InspectionID
· ItemID
· InspectionDate
· Inspection Result
· Etc

tblRepair
· RepairID
· ItemID
· RepairDate
· RepairMethod
· Etc

And so on with each type of action having its own table with fields
specific to the action.

That has proven somewhat cumbersome when I try to manipulate the data
to generate historical list of actions, figuring out what is in service
etc.

I've been toying with the idea of compressing all the action tables
into one with the following structure:

tblAction
· ActionID
· ItemID
· ActionDate
· Etc

The problem is that the "Etc" would essentially be the
conglomeration of all the fields in the various action oriented tables
in the original design. So for any given record, only a few of the
fields would be used. However, that seems wildly inefficient to me.

A compromise solution would be to keep the tblAction table for all
fields that are common, then create a 1=1 table for each action type
similar to my existing design. That would appear to be better
normalization, but I'm not sure it would help me solve the original
problems I was having.

Any suggestions?

Tom

[corey lawson]

Re: Normalization Question

Tom wrote:
[color=blue]
> Hi:
>
> I have a question regarding normalization of a database. I am trying
> to model the inspection and repair of items. Also need to track when
> they go in service, out of service and several other actions relating
> to the item. My first shot has the following structure:
>
> tblItem
> · ItemID
> · ItemName
> · Etc
>
> tblInspection
> · InspectionID
> · ItemID
> · InspectionDate
> · Inspection Result
> · Etc
>
> tblRepair
> · RepairID
> · ItemID
> · RepairDate
> · RepairMethod
> · Etc
>
> And so on with each type of action having its own table with fields
> specific to the action.
>
> That has proven somewhat cumbersome when I try to manipulate the data
> to generate historical list of actions, figuring out what is in service
> etc.
>
> I've been toying with the idea of compressing all the action tables
> into one with the following structure:
>
> tblAction
> · ActionID
> · ItemID
> · ActionDate
> · Etc
>
> The problem is that the "Etc" would essentially be the
> conglomeration of all the fields in the various action oriented tables
> in the original design. So for any given record, only a few of the
> fields would be used. However, that seems wildly inefficient to me.
>
> A compromise solution would be to keep the tblAction table for all
> fields that are common, then create a 1=1 table for each action type
> similar to my existing design. That would appear to be better
> normalization, but I'm not sure it would help me solve the original
> problems I was having.
>
> Any suggestions?
>
> Tom
>[/color]

Well, one way is to have an Items table, an ActionsItems table, and an
ActionTypes table (oddly enough, these are Ruby on Rails-compliant table
names... I loathe naming tables as "tblX" in the design window, as it
serves no purpose at all other than redundant redundancy).

ActionTypes just has Repair, Inspection, etc. values/records.

Items is as you did it.

ActionsItems is your working table:
ID
ItemID
ActionTypeID
DateStart
DateEnd
Notes
etc...

If you were doing it purely with Single Table Inheritance, then you
could also add a "Repair Method" field, Inspection Results field, etc
(but if it were me I'd just...stick the data into the Notes field in a
delimited fashion, be it xml or just item:value pairs).

You could then create queries/views on the ActionsItems tables to get
you your different action type datasets, i.e., Repairs, Inspections, etc.

With the DateStart and DateEnd fields, it then becomes trivial to
identify actions that are still "open", because they won't have a
DateEnd value, and you can calculate escalation values based off the
DateStart field vs the current date as well.