-1

I want to reference 2 of my columns in one table to a primary key from another table. Here is how it looks like in the db structure:

Users
uid (INT)  name (VARCHAR)
1          John Doe
2          Jane Doe

SystemProcesses
uid (INT)  name (VARCHAR)
1          Hitman
2          Cron

Logs
uid (INT)  modelType (VARCHAR)  modelUID (INT)  Action
1          Users                2               Jane Doe did this
2          Users                1               John Doe did that
3          SystemProcesses      1               Hitman just killed John Doe

How do I reference modelType and modelUID in Logs table to those Users and SystemProcesses?

If it's not possible, what is the the alternative?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
ln9187
  • 730
  • 1
  • 7
  • 23
  • This is a faq. Also google subtypes in sql/databases. – philipxy Dec 08 '16 at 23:55
  • Possible duplicate of [Foreign Key to multiple tables](http://stackoverflow.com/questions/7844460/foreign-key-to-multiple-tables) – philipxy Dec 08 '16 at 23:55
  • No, it's not a duplicate and it has nothing to do with subtypes. – ln9187 Dec 09 '16 at 17:20
  • My comments are correct. Eg you yourself have a "Type" column. Eg your own question is re "FKs [references] to multiple tables". (Of course, they're not FKs, but askers think they are.) Eg the current answer describes the situation as involving "polymorphic associations". That means multiple entity types are subtypes of another entity type. And the answer finds it relevant to say "a FK constraint supports only one referenced table". – philipxy Dec 09 '16 at 17:56
  • I don't think it should be called "polymorphic associations" either because, if you take a look at my example, User is not a subtype of System. – ln9187 Dec 09 '16 at 21:39
  • Users and system processes are subtypes of logged models. You just haven't explicitly put that into your design. Suggest you read the references in the comments & answer. – philipxy Dec 09 '16 at 21:57
  • Still having a hard time understand why Users and SystemProcesses are subtypes of logged model. This question, I believe, has nothing to do with association (except the fact that Logs has many Users and SystemProcesses). I wouldn't call them subtypes because they are not extending from any of each other. What part of the question do you think I should revise to help clarify because we are not talking about the same thing here. – ln9187 Dec 09 '16 at 22:08
  • 2
    You need to learn about subtyping in database design. Because *others* call what you are doing that. There are many uses of "type" & "polymorphic" in various languages, methods and products, all tied to things of some kind also being of another kind. Here, every user and every system process is a thing that can be logged. So the various idioms and facilities for dealing with that are relevant. (ER idioms & special "subtype" notation and ORMs' "subtype" & "polymorphic" facilities are specific ways to embody/implement the *conceptual* fact that a subtype relationship exists in the application.) – philipxy Dec 09 '16 at 22:20

1 Answers1

3

Don't use the same column for both foreign keys. This is sometimes called polymorphic associations, and it breaks rules of good database design.

It should be a clue that it's a bad design, that a FOREIGN KEY constraint supports only one referenced table. There is no support for polymorphic associations in standard SQL.

Instead, create two columns, one for a reference to Users, the other for a reference to SystemProcesses. One column per referenced table.

Logs
uid (INT)  UsersID (INT)  SystemProcessesID (INT) Action
1          2              NULL                    Jane Doe did this
2          1              NULL                    John Doe did that
3          1              1                       Hitman just killed John Doe

If there is no relevant reference for either the Users or SystemProcesses column, use NULL to indicate there's no applicable value.

You may like to review other questions I have answered about polymorphic associations.

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • But foreign keys can't have NULL values, can they? – ln9187 Dec 08 '16 at 20:45
  • Yes they can, unless the column is declared as `NOT NULL`. It's okay for a foreign key to be NULL. It's the `PRIMARY KEY` column(s) that must be `NOT NULL`. – Bill Karwin Dec 08 '16 at 20:48
  • 1
    The referenced columns of an SQL FK constraint must be non-null. The referencing columns can have nulls, in which case the constraint is considered satisfied. – philipxy Dec 08 '16 at 23:50
  • Although this answer makes senses in helping improve the design, but I am still trying to understand why it has anything to do with `polymorphic associations`. Take an example of Rails [https://github.com/technoweenie/acts_as_versioned](acts_as_versioned), I would like to accomplish the same thing without creating different Logs tables for different models. Eg: Users_Logs, SystemProcesses_Logs... – ln9187 Dec 09 '16 at 22:13
  • 2
    In your model, both Users and SystemProcesses are "things you reference from log events." So they act as if they have a common super-type, even if that isn't literally a class you have created. Think of [duck typing](https://en.wikipedia.org/wiki/Duck_typing). – Bill Karwin Dec 09 '16 at 22:21