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

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


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

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

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

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 :)

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:

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.

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:

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.

Just for the flat fish, here's what the query would look like in your existing tables:

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.

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