Re: Database History

lieut_data wrote:
> This database design problem has been plaguing my mind since I began
> using databases...
>
> Currently, Im using PHP & Mysql to design a
> trouble-ticketing/inventory management sytem. There will be a table of
> users, which will have inventory (i.e. Monitors, PC's, Laptops, etc..)
> bound to them. The hardware will have software bound to it (i.e.
> Windows 98/Microsoft Office/etc...)
>
> The trouble-ticketing sytem will refer to the hardware causing the
> problem, as well as the user having the hardware, and the user to whom
> the problem has been assigned. No problems, so far.
>
> To allow for the maximum user-friendliness, I had planned on referring
> (by primary key's) to the inventory/assigned user/etc. in the trouble
> ticket table. This would allow the user to view a ticket, then jump to
> the corresponding page regarding the inventory, and what not. (All
> tickets are saved, permantently)
>
> What happens when I delete a user (fired, left, who knows why?) Or
> what happens when that old PII300 gets tossed out the window? Clearly,
> I won't want the inventory table (or at least the interface to it) to
> show that old/unused user/hardware.
>
> Do I delete the entry from the table? Then what happens to the trouble
> ticket referring to it -- its stored keys now refers to a non-existent
> user/hardware.
>
> Or should I hard-code the information into each ticket regarding the
> user (I.e. dont use primary keys, but store "John" "Billcox" "IS/IT",
> etc..)
>
> Or should I not delete users/hardware at all? Just mark them
> "inactive", preventing them from showing up in the main list, where
> they are not used. But what if we want to use a username again, for a
> new employee?
>
> I have been reading about database normalization, which really helps
> to work out the design, but these interdatabase relationships are
> giving me headaches.
>
> Any enlightenment?
>
> Lieut_Data

     Unless a user's equipment is retired when the user dies, retires , is fired, etc. you should not delete the equipment from the database. You can handle this by changing the identity of the user to "Unassigned01", "Unassigned02" ... Unassignednn". Then when the equipment is assigned to a new user you only need change the identity of the user of the equipment.

     When equipment is retired it should be removed from the inventory, but you may wish to transfer the maintenance record to a service history table so that you can run a statistical analysis on failures and the required fixes. Received on Wed Mar 12 2003 - 05:51:38 CET