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 th开发者_开发技巧en 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

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.

I usually model this with something similar to inheritance in the object-oriented parlance. It would look something like this:

Products
   id (PK)

Widgets
    product_id (FK to Products.id) (PK)
    <widget specific columns>

Whatsits
    product_id (FK to Products.id) (PK)

Product_Types
    type_id
    product_id (FK to Products.id)

That way you can relate the Product Types to either product subtype.

Your design sounds a bit "loosey goosey", but maybe that's because you're just giving an example. I'd suggest finding out about the EAV model though and why you should avoid it. I can't tell if you're using that pattern or not, but it seems like you might.

This sounds like a design that will never work well in practice or under a typical user load. You really, really need to read why EAV tables are a very poor design choice. The flexibility of EAV is bought at a very high cost in developmebnt time to write long, complex, horrible queries and at the cost of severely degraded performance. EAV should only be used exceedingly rarely for a very few customizations by client. But if you have done your work correctly in design, 95% or more should be relationally modeled.

But if you are truly stuck with this, then the only way you can truly enforce the FK relationships is through triggers. Not enforcing them is an even worse idea and will lead to corrupted data.