Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Database History

Database History

From: lieut_data <>
Date: 11 Mar 2003 16:12:56 -0800
Message-ID: <>

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 Tue Mar 11 2003 - 18:12:56 CST

Original text of this message