Should I always design around foreign keys?

Question

For example I have a tag lookup table joining tags to 3 different types of tables (ObjectTypes). Each has tags, but they are not shared.

So I could do this

Tagid | ObjectType | ObjectId |

And when i join the table together I would just filter by object type before I joined.

Now I know that this would break the ability to do a foreign key on the ObjectId column sense is could be any of three tables.

Question is.. Is this a terrible thing? if so why?

The other option is to create a lookup table for each object, unless there is a better way.

Answer 1

Don't think about foreign keys, think about whether you want to enforce referential integrity at the database level. Without referential integrity enforced by the RDBMS, you're going to have to enforce it yourself in your application, or go out of your way to write complicated database constraints to do something the RDBMS already gives you in the form of foreign keys.

In practice, though, you're not going to have any good reason to store three conceptual tables in one big table. It's not a good idea to store more than one table's data in the same table just because they have the same columns. They have the same schema, but they are not the same table.