Database History
Date: 11 Mar 2003 16:12:56 -0800
Message-ID: <527869d5.0303111612.50027d77_at_posting.google.com>
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 Received on Wed Mar 12 2003 - 01:12:56 CET