Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2007
    Posts
    14

    Question Unanswered: Referential Integrity in a Shared Table

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2007
    Posts
    14
    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.
    Last edited by Genx; 11-30-07 at 07:07.

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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).

  5. #5
    Join Date
    Nov 2007
    Posts
    14
    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?
    Last edited by Genx; 11-30-07 at 07:23.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2007
    Posts
    14
    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.

  8. #8
    Join Date
    Nov 2007
    Posts
    14
    That was exactly what I was after, thanks for your help and pointers guys.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •