CODEWITHSUNDEEP
sqlsqlitedatabase-design
Francisc
Francisc

Reputation: 80505

SQlite Design Advice

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.

Upvotes: 1

Views: 90

Answers (2)

Tim
Tim

Reputation: 5421

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)

Upvotes: 2

Mike DeSimone
Mike DeSimone

Reputation: 42825

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.

Upvotes: 1

Related Questions