SQlite Design Advice

Question

What is the best way of dealing with foreign keys in a table that can be from two different tables? To be more clear:
T1(id,c1,c2) - c1 and c2 are from either T2 or T3
T2(id,name)
T3(id,name)
Consider T2 and T3 have to be different tables. And if c1 is from T1 (for example), c2 can be from either T2 or T3.

Should I:

1. Create a new table that holds the associations and have c1 and c2 point to an id from that table? i.e. T4(id,id_from_T1,id_from_T2_or_T3,what_table)

2. Create 2 columns, c1_parent, c2_parent and make a convention like if it is 1 then it's from T2 if it's 2 then it's from T3.

Or is there an entirely new better way?

Thank you.

• Jacob: Do T2 and T3 have the same structure?
• Francisc: No, I'd have to use IF and CASE. But for the moment they both share than name column which is all I need. Not sure in the future.

Answer 1

Conditional foreign keys are non-standard SQL. You should find a way to amalgamate your NAMES tables. If necessary, NAMES could have a composite key.

For example, instead of DEFENDERS and ATTACKERS tables, you'd have amalgamated PLAYERS table with a column that indicated whether the player was offense or defense:

 PLAYERS
 OffenseOrDefense
 PlayerName
 primary key(OffenseOrDefense,PlayerName)
 DREAM TEAM
 position
 offenseOrDefense
 playerName
 foreign key(offenseOrDefense,PlayerName) references PLAYERS(offenseOrDefense,PlayerName)

Answer 2

There isn't a good mechanism for guaranteeing that id is not duplicated across all three tables. So you're better off with T1 as:

T1(id,c1_tbl,c1_id,c2_tbl,c2_id)

Alternatively, refactor your table design so that a given foreign key can only come from one specific table. (I can't give specifics because you didn't describe the goals of this schema.)

Either of these will make life easier when it comes time to JOIN.

• Francisc: Thank you Mike. But can you condition JOINs based on a column?
• Mike DeSimone: Sure. I don't write a lot of SQL myself, but I'd imagine it would be something like SELECT ... FROM T1, T2 WHERE T1.c1_tbl = 1 INNER JOIN T1.c1_id = T2.id or so. The Django contenttypes framework uses something like this. It creates a table that represents each table that can be used in this way, assigning each table an ID number. Then you can reference any record in any table with a table-ID and ID-within-table combination (a "generic foreign key"), allowing the IDs in the tables to be standard auto-increment fields.