CODEWITHSUNDEEP
mysqlsql
Stephen Fox
Stephen Fox

Reputation: 87

Is it acceptable to have NULL foreign keys?

This should be a simple question I think, but is it OK to have NULL foreign keys?

I guess to elaborate, let's say I'm making a database for users and different types of users require different data sets... what would be the best practice to design this database?

This was my thought, as a rough example: (am i correct or way off?)

"users": id | type (ie. '1' for basic, '2' for advanced) | basic_id (nullable foreign key) | advanced_id (nullable foreign key) | email | name | address | phone (etc etc)

"users_basic": id | user_id (foreign key) | (other data only required for basic users)

"users_advanced" id | user_id (forgein key) | (other data only required for advanced users)

I get the feeling it's bad design cause there's no way to get all the data in one query without checking what type of user it is first, but I really don't like the idea of having ONE table with a ton of NULL data. What is the best way to design this?

Upvotes: 0

Views: 338

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Of course it is fine to have NULL foreign keys.

In your case, though, I'd be inclined to do one of two things. If there really aren't very many columns for the basic and advanced users, you can just include them in the users table. This would be the typical approach.

Otherwise, you can declare user_id as the primary key in all three tables, and still have a foreign key relationship from the secondary tables (users_basic and users_advanced) to the primary (users). Maintaining the distinctiveness of the relationship is tricky in MySQL and probably not worth doing.

Upvotes: 1

Related Questions