I am looking for a best-practices design case for the following:

I am working on a web-app's (PHP) back-end database (MySQL). This db already contains tables named SERVER and WORKSTATION. Each table has an autonumber int pk field called ID.

It occurs to me that one day that, as I added new types of inventory to this db, I will be creating new tables (e.g. PRINTER). This leads me to think about adding a table called INVENTORY. INVENTORY will also have a unique pk ID. It will also have an int field called INV_ID which store the ID from the SERVER, or WORKSTATION (or PRINTER, or whatever) table. It will have a fieldcalled INV_TYPE, which will indicate which table this ID comes from (SERVER, or WORKSTATION, or PRINTER, or whatever).

While each of these tables has fields in common (Name, Location, etc.), they store a lot of information specific to the type of item. BUT... I want to link them to the same table. I could then move all the common fields to the INVENTORY table.

My question is: is this good or bad design? Does the fact that the fk field in INVENTORY holds an ID that could be the foreign key to one of a number of tables denormalize the database? Is the fact that the logic will occur on the web app side (which table to link to) incorrect? Is it required that a database be totally self-contained and self-describing (i.e. there would be no way from examining the schema to determine the relationship of INVENTORY to the other tables)

Any thoughts or suggestions are appreciated.

Thanks,

- Jay

Sounds OK so far.
No. The primary key of the INVENTORY table can be the foreign key in as many tables as you would like.
In my opinion, yes. What makes you think this has to be on the application side?
Maybe I misunderstand. Why cannot you not enforce these relationships within the database schema?

with appologies to Dr. Tolkein, who MUST be spinning about now.

Got to agree with blindman that it's not a good idea to attempt to place RI in the application layer. Put it in the database, close to the data, where it belongs. That way, even if you screw up the front end, or when you're importing data, or when someone else who DOESN'T know the RI rules for the database writes another app to poke some data into the database, you won't lose RI.

but blindman, that's not what he's doing, he's not linking those subsidiary tables with FKs that point back the the main INVENTORY table's PK, rather, he's thinking of a field in the INVENTORY table, which will sometimes have a value from this table, sometimes from that one, etc. -- and hence cannot actually be declared as a FK

which, in my opinion, is bad design

That can be implemented with GUIDs. I have done it successfully.

r937 is totally correct as to how I am looking to have this work.

Servers, workstations and whatever else we may add are items in inventory, with common properties/fields such as Name and Location. I would like to store all these properties in the same table called INVENTORY. However, since they posses different detail info (SERVER has a Raid_Configuration field, for instance, and WORKSTATION doesn't), I need a way to relate the two master-detail tables.

The reason I want to do this is to be able to handle all the things in INVENTORY as a group, say on a summary screen. When adding a new item, say a workstation, the info would get added to the WORKSTATION table as well as a new record being created in INVENTORY. This kind of data setup would then nicely inform the design of objects on the programming side.

Is there any way to build this kind of relationship into the database schema?

Thanks for the help and quick responses,

- Jay

sure, just put the FK into the child table

each dependent table e.g. WORKSTATION will have an INV_ID field which contains the value of the INVENTORY table's PK field

whether the INVENTORY table needs to have a discriminator field (which tells you what type of thing it is) is up to you

to retrieve all fields pertinent to inventory items, use LEFT OUTER JOINs to join the INVENTORY table to all of its dependent tables, and notice that for any given item, only one of the tables will have a match

mebbe Im being thick here
but WHY do you have a separate table for servers and workstations

are you planning to add a new tabekl for each computer device which isnat a server or workstation (eg router, switch, printer, printer server)

a device exists if its exists in the warehouse then its in inventory, if it exists in the office in use then its an asset. by all means subclass the product or item to identify what type of computer device it is, but it doens't make data modelling sense to have a separate table for each item type.

Having a "parent" table like inventory, with many potential "child" tables (workstation, server, printer, etc) is an advanced, but fairly common normalization technique called "entity decomposition" in Data Modeling. Is is directly comparable to the technique known as "sub-classing" in Object Oriented Design.

What you are doing is both possible and more importantly it is correct!

-PatP

maybe not to you, eh

no it doesn't.. but I'm always keen to learn

The reason to have a separate table for each item type is that each of these items has fields which are not in common. You could create an INVENTORY table which combined all fields (instead of just the common ones), but then how would you tell which were appropriate for each device? For instance, INVENTORY would have a Raid_configuration field which would be appopropriate for server records, but not for router records.

As time went on and you wanted to add new types of items to your inventory (Blackberries, staplers, whatever) you would need to keep adding new fields.

Also, the logic for which fields get filled in for each type of item would reside wholly in the programming logic.

it does anyway, whether you have one master table or several child tables

in fact, with one master table, it's somewhat easier



depending on how many "unique" fields there are, i might just go ahead and have only one master table

say the common table has id and name, and all the other data is dependent on what type of item it is, and there are dozens of these fields -- then i might have common/child tables

say the master table has id and name and a couple dozen common fields (date acquired, purchase cost, order number, etc.) and there are only a few unique columns per type of item -- then i might have just the one master table with NULLable unique fields

here's what einstein would've called a gedankenexperiment --

imagine what the query looks like to return full details of all items from both common and child tables, and picture what the result rows look like, after the query has executed its several LEFT OUTER JOINs, and compare this to the query to return full details of all items from a master table, in which the query will simply return full rows -- aren't there going to be NULLs in exactly the same places in the result rows?

Ah yes, but NULL values in a result set and NULL values stored in a column are two very different things in relational algebra. As our fine friend Fabian would point out, NULL values can't be stored in a relational entity.

Having lit that fuse, I just remembered somewhere across town that I REALLY need to be, very soon!

-PatP

now there's a brilliant tautology that i'm not sure everyone knows, so it bears repeating -- NULL is not equal to anything, even another NULL, so of course NULL values in a result set and NULL values stored in a column are two very different things

and anyway, so what? when was the last time you pulled stuff out of a database using relational algebra? hint: never -- you used SQL

and your fine friend has gone totally wacky, zany, bonkers, cuckoo, round the bend -- and while i could take a cheap shot and say that this is due entirely to his preoccupation with how many non-NULL relations can do the polka on the head of a pin, i shall merely point you to what has been occupying his thoughts for the last couple of years, ever since he gave up debunking -- The Fall of Knowledge and Reason

a word to the wise is sufficient, eh