CODEWITHSUNDEEP
mysqlsqldatabase
Tony
Tony

Reputation: 11

polymorphic association alternative

I'm trying to design a part of my database which should cover users login. My users can login with "local", "facebook" or "google" account.

What I have is a table users that contains two columns, login_type login_id. The values of login_type can be "local", "facebook" or "google" which refers to three tables: local, facebook and google. login_id is the id of the login_type referenced table.

I don't like this polymorphic association and would redesign this part to keep the database simple and coherent by creating tables that references as usual with foreign key.

Appreciate any suggestion

Regards

Upvotes: 1

Views: 1561

Answers (1)

Mike Nakis
Mike Nakis

Reputation: 61979

What you are doing is a trick known in Object Relational Mapping (ORM) systems as a discriminator column. The problem with it, as you already understand, is that referential integrity goes out the window, because you cannot declare your login_id as being a foreign key that maps to another table, because it may map to one of three possible tables, and the table that it maps to is chosen by the value of the login_type column.

The way to do this correctly might seem a bit strange, but it does guarantee referential integrity.

Table users columns:
     id                    primary key
     local_users_id        foreign key, references local_users(id)
     facebook_users_id     foreign key, references facebook_users(id)
     google_users_id       foreign key, references google_users(id)

So, the login_type column is abandoned, and instead you introduce three nullable foreign keys: local_users_id, facebook_users_id, and google_users_id. Only one of them may be non-null.

You can make sure that only one of them is non-null in code, or even in the database, with a trigger or perhaps even with a constraint.

Upvotes: 4

Related Questions