Q:

Foreign Keys Changing Based on a Field

In SQL (specifically mysql) consider a case when I have a table with a enumeration (name, relation) called type and an int called idtype.
I then have another table called names which has a field idname and another table called relations with a field idrelation.
Essentially if type == name then idtype is a foreign key for idname and if type = relation then idtype is a foreign key for idrelation. Is there a way to specify such a foreign key relationship? Or/And is there a better, more conventional way to represent such a relationship in mysql?
Thanks,
David

A:

The problem is that you're storing two differen types of information in a single table - almost always a bad idea (IMO).  You should break up your first table into two. Another option is to have two fields nameID, and typeID - but I wouldn't recommend doing it that way.  Without more specifics on your tables, I can't offer a more concrete answer.

