-1

I am developing a database schema and stuck on the fence about one part of the developing schema. I have foreign keys going to certain tables, with a lot of the tables containing a foreign key to one central table.

Two of the tables I am designing, I set up with an "ID type" look up (foreign key to list the types/tables the ID belongs to) and then an FK ID value. I'm wondering if I should do that for all table foreign keys, to be consistent. For these 2 tables, they might need to be joined to a different table, depending on what the record will be about. Think people being tied to different aspects of a process, so that person record would be linked back to 1 table or a different one, depending on where they are involved in the process.

For other tables, they really are only going to link to that main central table. Should I just leave an explicit foreign key, directly to that table, or make it consistently an ID type/table and an FK ID which is linked based on the ID type table?

If this doesn't make sense, let me know and I'll try to explain better.

Thanks!

missscripty
  • 529
  • 2
  • 11
  • 30
  • 1
    You are not clear. Eg "I set up with an "ID type" look up ([...]) and then an FK ID value"--we don't know what you mean by "set up" and we don't know what FKs to where. Make referents clear. Please give DDL, example data & table meanings. PS FKs are constraints. They say subrows also appear elsewhere. They tell the DBMS what database states can arise *given* what tables mean & what business situations can arise. Design the *table meanings*. Use ids to identify/name business entities/things. Typically there's no need for an association table id, its entities form a PK. FKs last. – philipxy Jun 17 '17 at 03:20
  • According to the first sentence of the self-answer, this is about (?!) "a foreign key column, that links to different tables, based on another table identifier column (See my comment there.) So it's a duplicate of [MySQL foreign key using more than one field to reference to a primary key from another table](https://stackoverflow.com/questions/41047790/mysql-foreign-key-using-more-than-one-field-to-reference-to-a-primary-key-from-a) and/or [Database design - articles, blog posts, photos, stories](https://stackoverflow.com/questions/4969133/database-design-articles-blog-posts-photos-stories). – philipxy Jun 17 '17 at 03:34

1 Answers1

0

Talking to a colleague and it was advised that I not have a foreign key column, that links to different tables, based on another table identifier column. Apparently this can get dangerous quickly, as the ID could mistakenly get linked to the wrong table, if people aren't careful.

The advice is to have the people table, and then multiple people cross-reference tables, one for each area of the data, where a person needs to be linked to. Each CR table will have a non-null foreign key, and records only to the 1 table the FK links to. There will also be a person type ID, so that a person can have more than one type/roll, within as many CRs where they need to exist.

If anyone disagrees, I'm more than happy to get as many takes as I can.

missscripty
  • 529
  • 2
  • 11
  • 30
  • There's no inkling in your question that it's about what the first sentence in this answer is about. That FK anti-pattern is usually the wrong way to model "SQL/database subtypes". (Google my stackoverflow comments about that. The rest of this answer is just as unclear as your question. You need to stop labeling things that you *haven't said* by words that come to mind (eg "area of the data") and *say* what you mean. Examples help but you still need to say what they are examples *of*. – philipxy Jun 17 '17 at 03:31
  • A FK constraint holds in a design when subrow values for a list of columns must appear for another list of columns. We declare FKs so the DBMS rejects invalid updates. If a column's values are only restricted to being in one of several other places (per a type column) then that is not an example of a FK holding; it's a different constraint. Distinct nullable column sets for each target table would be FKs. The type column could indicate which FK(s) (or tables, if there's one per table) hold. So could just whether each FK is null. But those are all anti-patterns for subtyping. Thus this answer. – philipxy Jun 20 '17 at 03:05