CODEWITHSUNDEEP
database-design
john
john

Reputation: 35400

What's the best way to model 1:1 optional data in this scenario?

I want to create an authentication system similar to Stackoverflow's.

Users can create/login with an account on my site or create/login with an openid from various providers.

A typical schema might look something like:

Table: users
====================

id
email
password
date_created

Table: users_openids
====================

id
user_id
openid_url

The problem with this schema is that email/password are only for my site accounts.

Is the schema I am proposing below the optimal way to do this?

Table: users
====================
id
account_type_id
date_created

Table: account_types
====================
id
account (enum('mine','openid'))

Table: users_openids
====================
id
account_type_id
user_id
openid_url

Table: user_mine
====================
id
account_type_id
user_id
username
password

Table: user_attributes
====================
id
user_id
email
nickname
full_name
dob
gender

It seems a little weird to me, any thoughts/criticisms/optimizations/recommendations would be greatly appreciated.

Upvotes: 1

Views: 69

Answers (1)

SingleNegationElimination
SingleNegationElimination

Reputation: 156238

optional data can exist as either nullable fields in the parent table, or if multiple columns are optional together, then you should make a second table with a the primary key that is also foreign key referencing the parent table.

Upvotes: 1

Related Questions