Hi Guys,

My first question is going to be slightly random ...

Lets say you have a phone numbers table structured as such:

numberId
tableId
recordId
phoneNumber

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.

the FK is not possible

any time you find yourself storing a table name or a column name (metadata), you are invariably making a design error

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...

[EDIT]
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?
[/EDIT]

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.

well then you have two tables where don't you? One for employee_numbers and one for business_numbers (assuming you don't put this in the businesses table as columns).

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?

create a "thing" supertype table, which will have all common columns (e.g. phone)

then create multiple subtype tables and relate them to the supertable

do a search for supertype/subtype for more information

Something along that line had crossed my mind but it was a bit of an iffy concept in my head.

Thanks for your help, will be back a little later after i've thoroughly educated myself.

That was exactly what I was after, thanks for your help and pointers guys.