I am building a database and have hit a problem while assigning foreign keys.
Assume I have a table for photo albums as follows:
albums( aid integer,
I also have tables for user, group, events (like facebook). Each of these tables has a PK (uid, gid, eid) which i want to use as a FK in the albums table in the attribute locationid where the attribute type is the name of the table. This lets me have an album for photos, users or groups. The problem is that I am unable to set locationid to be a FK to uid & pid & gid at the same time.
If I don't set locationid as a FK then it all works, but that is bad design.
Any advice on how the change my design?