Unanswered: Referential Integrity in a Shared Table
My first question is going to be slightly random ...
Lets say you have a phone numbers table structured as such:
Where tableId is the Id of the source table, recordId is the PK of the source record in the source table and numberId is the PK field for the number table...
Assuming that the tableId is only used in queries and is not explicitly defined as a field in the parent table, how would you enforce referential integrity using foreign keys and / or is this possible?
Cheers for any help, suggestions or alternative trains of thought.
See I figured that, but say you have businesses, and individual contacts within that business - you might have multiple numbers for the business itself and then multiple numbers for each individual contact -> If the business is deleted you would want the businesses numbers to be deleted, but if an employee was deleted, you'd want the individual numbers relating to that employee removed...
Or maybe a clearer example, lets say you have prospective clients and current clients - prospective clients and current clients both have very different information that relates to them. For example current clients are real clients of your business they have invoices, jobs etc. related to them. Prospective clients have scant details. One thing they share though is the requirement to store multiple numbers... What would be the most appropriate way to structure this?
I've been with FileMaker for a few years so bear with with me as I'm just trying to adjust to SQL based structure.
Sorry, I'm not trying to frustrate anyone here, but lets say you then have 5 very different categories of "things" (use above examples) that require you to store multiple numbers, addresses, images, invoices, categories, mailing lists, contact histories etc.
If the solution is to create a related table for each and every "thing", you then start to have an alarming number of tables... Rather than 10 tables, you might have 70, most of which store exactly the same information.
What if i then wanted to do a global search for a phone number, or a particular address - would i need to run 5 queries?