Table Organization and Data Integrity Help
I have a dilemma that's been bugging me for some time now, so I thought I'd put it out for bid ;)
I have an Invoices table that is related to a Client/Company table which is then related to Contacts (individuals within the organization). I also have a Venues table that tells where our items are being delivered. This is also related separately to the Invoices table.
The problem is, many times a Venue (Such as The Lizard Lounge) is also the Client ordering the invoice. This means that with the way it's set up now, I have The Lizard Lounge in both the Company table and the Venues table. Obviously, this is a data integrity nightmare.
I need to be able to use the Venue by a lookup for the "Venue" field on the invoices layer as well as using "Customer" from the Company table, also via lookup. When the lookup occurs, venue populates relevant technical information (load in dock, stage dimensions, production contacts) and relevant company information (billing information, terms, etc).
Is another linking table the best way to do this? Is there a clever way I can do it without a linking table? Or maybe a script that runs on the venues table when a company is classified as a venue?
I'm stuck. I've been thinking about it for too long and I've backed myself into a mental corner!