Are you suggesting that I would need to create 3 join tables to connect everything to a unified Barcodes table? I tried that and I'm being prompted to create extra table occurrences because Clients, Programs, and Output are already related to each other.
Also, the relationship between Client and Barcodes is not many-to-many, nor is the relationship between Program and Barcode or Output and barcode. Each barcoded item would have to choose to be under either Client, Program, or Output.
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?
No. In many cases, this is the correct approach. Think of a join table as a place where each record represents a unique combination of parent records. If you have a three-way join where this is true, then multiple keys is perfectly fine.
Has your question been answered? When I read your question, I thought of a solution.
What is your suggestion? I'm still using 3 separate foreign keys in my barcodes table
Thanks for the quick reply.
1.) Do you still have separate tables for Sources, Tapes, and Hard Drives?
2.) What is the table structure in which you are using the 3 separate foreign keys?
3.) How is the database performing speed wise? How is it performing in meeting your informational need?
4.) Could you send me a screen shot of your relationship graph? (It's ok, if not)
5.) Are Tapes and Hard Drives both storage methods?
6.) Do you put products and raw material on Tapes and Hard Drives?
Just six simple questions, lol. Sorry about that. I'm just trying to wrap my head around what you are trying to accomplish.
1. No, I don't have separate tables for sources, tapes, and hard drives. The only real reason against that was because barcodes are unique across all of the tables and I wanted to provide users a way to scan the barcode once and have it search all 3 tables
2 & 4. I've attached a screenshot of a sample of the relationships in my graph (the full graph would have been too hard to explain). Basically I have 1 barcodes table with 3 fields used as foreign keys. One to relate to clients, one for programs, and one for outputs. I use a calculation field that looks at the values (or empty values) in these fields to give me a "type" that classifies the barcode as either a source, tape, or hard drive.
3. Speed hasn't been an issue for me just yet. There are currently about 2500 records in the barcode table.
5 & 6. No, tapes are either blank stock, or contain the final video content for TV shows. Hard drives store various files, but I was not looking at indexing the contents of hard drives in this solution.