Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Nov 2011
    Location
    NYC area
    Posts
    2
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Table Structure Help, Please

    I am looking for advice concerning, I think, foreign keys.

    I am a loooong-time developer but my experience with SQL was brief and long ago (about 15 years)




    Here's a simplified example of what I think I want to do:

    I have 5 tables in a db.
    3 are self-contained,
    1 has references to two of the tables the self-contained tables
    1 is mostly a table of relationships between other tables.

    People, Companies, PhoneNumbers, Committees & CommitteeMembers
    With the following structures

    People (self-contained)
    id
    name
    address
    date of birth
    etc

    Companies (self-contained)
    id
    name
    address
    industry
    etc

    Committees (self-contained)
    Id
    Name
    Description


    PhoneNumbers (references to People and Companies)
    id
    fromtable
    table_id
    phone_number
    type
    Sequence


    CommitteeMembers (Is mostly a link between the Committee table and the People table -- with a some reference to companies possible. In the truest sense of the word, it is a relational table)
    Id
    Committee_id
    fromtable
    table_id
    Date joined
    Role
    Date left


    In the People table I have
    Code:
    id   name          address     DOB    
    1    John Doe     Main St     19900101
    2    Mary Doe     Main St    19910202
    3    Joe Blogs     Broadway  19920303
    4    Santa Claus  North Pole     10101
    In the Companies table I have

    Code:
    
    id     name                 address      industry
    1      Acme Widgets     First st      Manufacturing
    2      Beta Electronics  Second St  High Tech
    3      Toy Makers         N. Pole      Manufacturing
    
    In the Committees table I have
    Code:
    
    Id      Name      Description
    1       CREEP     Committee to Re-Elect the President
    2       LPG       Lollipop guild
    Here's the Foreign-key candidate, I think.

    For the table PhoneNumbers

    Code:
    Id  fromtable table_id  phone_number   type    Sequence
    1   1           1           888-555-1212    Home    1
    2   1           1           877-222-3333    Work    1
    3   1           1           888-555-2323    Home   2
    4   1           2           888-555-2323    Home   1
    5   2           1           877-222-3333    Main    1
    6   1           4           866-111-1111    Mobile  1
    So the first 3 entries relate to the Peoples table, which I am referring to as table 1, and relates to John Doe, (from table, ID #1) and are his primary home number, his primary work number and his secondary home number.
    the 4th entry is Mary Doe's Primary home number. (Which, coincidentally, is John Doe's secondary home number)
    The 5th entry refers to the Companies table (referred to as table 2) and is the Main phone number for Acme Widgets, which is also John Doe's primary work number.

    The main thing I don't understand is, how do I make the combination of "from table" and "table_id" in the PhoneNumbers table a foreign key, since it has to be the combination of the 2 values, yet the 'fromtable' value does not come from the dependent table. I suppose I need to ask the question, do I even have to/want to make it a foreign key?

    Furthermore, I suppose that I could make the 'fromtable' values the actual names of the tables ('People' and 'Companies') but that seems like a waste of space when I could use an int instead.


    Obviously, (I think), I want to be able to quickly look up the phone number of any of the entries in the people and companies tables but don't want to have a peoplesphonenumber table and a companiesphonenumber table.

    (I realize I could combine the people and the companies tables into one, but, in practice, they will be dissimlar enough that there's good reason to have separate tables.)

    I am also interested in knowing if there's a convenient way to autoincrement the "sequence" value in the PhoneNumbers table when there's a previous entry with the same set of fromtable+table_id+type




    Here's the almost purely-relational table:
    CommitteeMembers has
    Code:
    
    Id     Committee_id  fromtable  table_id Date            Joined  Role            Date left
    1      1                  1             4          10000101     Chair          NULL
    2      1                  2             3          20010101     Toy supplier NULL
    3      2                  1             3          19991231     Member       20000101
    
    This is similar to the PhoneNumbers table in that it has the "fromtable" and "table_id" fields.
    What this shows is committee #1, CREEP, has 2 members -- 1 is a person and 1 is a company.
    Committee #2, LPG, has has 1 member but he left a while ago.
    The usage of this will differ from the PhoneNumbers table substantially

    The PhoneNumbers data will be mostly a look-up given a people ID or a Company ID (meaning, "I know the company I am interested in, get their phone number for me")
    The Committee member lookups will be mostly (or only) of 2 types:
    Show me all the members of a given committee
    Or
    Show me all the committees of which a given people is a member.

    I think the relevant difference between this table and the phonenumber table is I want to INDEX the Committee_id to make the first search faster.

    Thanks in advance -- even for reading through the whole post down to here

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,121
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Well, for starters, it's usually considered bad practice to use a "discriminated foreign key". That is, using both tableid/name and the primary key value to describe a foreign key.

    For one thing, it means that the database can't help you enforce referential integrity.

    As ugly as it may seem, it's usually better to do something like this:
    Code:
    CREATE TABLE PhoneNumbers (
        id  int AUTO_INCREMENT PRIMARY KEY,
        peopleID INT NULL REFERENCES People(id) CASCADE DELETE,
        companyID INT NULL REFERENCES Company(id) CASCADE DELETE,
        phone_number  ...
        type  ...
       Sequence  ...
    );
    Notice the CASCADE DELETE.

    With the above constraints, and assuming you aren't using the MyISAM engine with MySQL, if you attempted to add a record to the Phone table and tried to use a peopleID or companyID value that didn't exist in those tables, you would get an error. Further, if you delete a record from People or Company and there is a reference to the id of the record from the Phone table, then the Phone record would be deleted, automatically.

    In other words, you'd never have a situation where a Phone record would exist with no link to another record.

    If you omit the CASCADE DELETE, then instead of the automatic deletion from Phone, you would not be *ALLOWED* to delete the referenced record in People or Company until you specifically deleted the referencing Phone record.

    All of this is called "referential integrity" and most database designers and coders depend on it.

    Okay, not that I've lectured a bit off topic, let me go read the post again.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Dinosaur59 (11-05-2011)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,121
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Well, I guess that wasn't really off topic. I guess may overall answer to you would be: Don't do it that way.

    Purists would frown on allowing NULL in a foreign key, but as a practical matter it works well and allows the non-null foreign keys to continue providing referential integrity.

    The only downside this would be if you ever wanted to, say, make a phone directory.

    For that, you would then have to do something like this:
    Code:
    SELECT PH.phone_number, 
           IF( P.name IS NULL, 'Company', 'Person' ) AS phoneBelongsTo,
           IFNULL( P.name, C.name ) AS theName
    FROM Phone AS PH
    LEFT JOIN People AS P ON PH.peopleID = P.id
    LEFT JOIN Companies AS C ON PH.companyID = C.id
    Which is only mildly ugly and no uglier that what you would have to do using your tableid/id system.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,121
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Just for the flat fish, here's what the query would look like in your existing tables:
    Code:
    SELECT PH.phone_number, 
           IF( P.name IS NULL, 'Company', 'Person' ) AS phoneBelongsTo,
           IFNULL( P.name, C.name ) AS theName
    FROM Phone AS PH
    LEFT JOIN People AS P ON ( PH.fromTable = 1 AND PH.table_id = P.id )
    LEFT JOIN Companies AS C ON ( PH.fromTable = 2 AND PH.table_id = C.id )
    Even this query is a bit uglier and since you lose the referential integrity, I don't think it's worth using.

    Opinion. Seek out the real database experts, though, and see what the balance of opinion is.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Dinosaur59 (11-05-2011)

  • #5
    New to the CF scene
    Join Date
    Nov 2011
    Location
    NYC area
    Posts
    2
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks for taking the time to decipher my post and for the analysis.
    And thanks for cutting me a brake by not disparaging my question. ;-)

    Sometimes ugly is the only way and sometimes ugly is the best way.

    Besides, if it doesn't work out to be the best solution, I can redo it.

    One of the most successful people I've ever known is fond of saying, "Anything worth doing in the first place is worth redoing."

    I appreciate your help


  •  

    Posting Permissions

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