How can I handle conditional fields?

Question

I am in the process of designing a database for my company to assist in asset management. I'm not a database administrator, but I had some previous course experience with databases. Our assets are used in the field at various job sites or in a warehouse or vendor facility. My question relates to best practices for handling the case where a table can have one of two fields completed. In my case, the asset can be at a shop or at a job.

The way I am considering handling this is adding a flag field that indicates whether the asset is on a job. If true, then the JOB_ID column should have a value and the SHOP_ID should be null. If false, the opposite should be the case. Those IDs will reference the appropriate JOB and SHOP tables.

My question is, am I approaching this from the right direction? Are there best practices for these cases? I'm assuming tracking inventory is far from a novel problem.

Here is the schema for the ASSET table with some columns excluded for brevity's sake: ASSET(ASSET_ID, IS_ON_JOB, JOB_ID, SHOP_ID)

• Philᵀᴹ: Might be better to have a JOB_ASSETS table, or similar
• Michael Green: A JOB_ASSEST table does not, in itself, preclude the same ASSET from also being in a SHOP_ASSET table. Application code and / or trigger would be required to ensure any one ASSET was only ever in one of these tables at any point in time.

Answer 1

Your approach is reasonable. Put a check constraint on the ASSET table to ensure exactly one of your foreign keys is ever set at any point in time.

The flag is redundant since the same information is captured by which of the foreign keys is NULL. Keeping it just adds maintenance overhead to the application. Eventually it will become inconsistent and then you'll have a mess to sort out.

An alternative design is to combine some of the information from SHOP and JOB into a LOCATION table. The schema then becomes

ASSET(ASSET_ID, LOCATION_ID)
LOCATION(LOCATION_ID, NAME)
JOB(JOB_ID, LOCATION_ID)
SHOP(SHOP_ID, LOCATION_ID)

Put one row in LOCATION for each "job site or .. warehouse or vendor facility," as you say in your question.