I'm having some trouble structuring my database. It seems to be getting more complicated than it needs to and I'm hoping that multiple table occurrences can fix my problem, but I'm not sure if I would be using them properly.
The basic structure of my relationships is Clients > Programs > Episodes > Outputs > Tapes. I have added another table under Programs called “Sources”, and I’m about to add a 3rd table under clients called “Hard Drives”. It seems to me that the Tapes, Sources, and Hard Drives table should actually be only 1 table because each record in those tables is assigned to a unique barcode. The problem however, is that there is no one-to-many relationship that would apply to all 3 types of items that have barcodes.
If I combined these tables in a single Barcodes table and used 3 different foreign key fields to make each Table Occurrence a child of it's proper table in my main structure, would that be bad database structure?
For example, I would have a field _fkClientID to connect one table occurrence to the Client's table, so that all items with a matching ClientID field, but but another field _fkProgramID for the records that need to be children of my Program table, and another field for _fkOutputID, etc.
The main advantage of this would be that when I'm searching for barcodes, I only have to search 1 table, then I can more easily check which parent table the record is related to, and I could more easily ensure that users did not enter duplicate barcodes into different tables. Will this work or am I missing something?