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)