If my entity has a (0-1):1 relation to another entity, how would I model that in the database?

For example, lets say I have an entity called user and an entity called profile_picture. A user may have none or one profile picture.

So I thought, I would just create a table called "user" with this fields:

user: user_id, profile_picture_id (I left all other attributes like name, email, etc. away, to simplify this)

Ok, so if an user would ha开发者_开发百科ve no profile_picture, it's id would be NULL in my relational model. Now someone told me that I have to avoid setting anything to NULL, because NULL is "bad".

What do you think about this? Do I have to take off that profile_picture_id from the user table and create a link-table like user__profile_picture with user_id, profile_picture_id?

Which would be considered to be "better practice" in database design?

This is a perfectly reasonable model. True, you can take the approach of creating a join table for a 1:1 relationship (or, somewhat better, you could put user_id in the profile_picture table), but unless you think that very few users will have profile pictures then that's likely a needless complication.

Readability is an important component in relational design. Do you consider the profile picture to be an attribute of the user, or the user to be an attribute of the profile picture? You start from what makes logical sense, then optimize away the intuitive design as you find it necessary through performance testing. Don't prematurely optimize.

NULL isn't "bad". It means "I don't know." It's not wrong for you or your schema to admit it.

"NULL is bad" is a rather poor excuse for a reason to do (or not do) something.

That said, you may want to model this as a dependent table, where the user_id is both the primary key and a foreign key to the existing table.

Something like this:

  Users                     UserPicture                   Picture
----------------          --------------------          -------------------
| User_Id (PK) |__________| User_Id (PK, FK) |__________| Picture_Id (PK) |
| ...          |          | Picture_Id (FK)  |          | ...             |
----------------          --------------------          -------------------

Or, if pictures are dependent objects (don't have a meaningful lifetime independent of users) merge the UserPicture and Picture tables, with User_Id as the PK and discard the Picture_Id.

Actually, looking at it again, this really doesn't gain you anything - you have to do a left join vs. having a null column, so the other scenario (put the User_Id in the Picture table) or just leave the Picture_Id right in the Users table both make just as much sense.

Your user table should not have a nullable field called profile_picture_id. It would be better to have a user_id column in the profile_picture table. It should of course be a foreign key to the user table.

Since when is a nullable foreign key relationship "bad?" Honestly introducing another table here seems kind of silly since there's no possibility to have more than one profile picture. Your current schema is more than acceptable. The "null is bad" argument doesn't hold any water in my book.

If you're looking for a slightly better schema, then you could do something like drop the "profile_picture_id" column from the users table, and then make a "user_id" column in the pictures table with a foreign key relationship back to users. Then you could even enforce a UNIQUE constraint on the user_id foreign key column so that you can't have more than one instance of a user_id in that table.

EDIT: It's also worth noting that this alternate schema could be a little bit more future-proof should you decide to allow users to have more than one profile picture in the future. You can simply drop the UNIQUE constraint on the foreign key and you're done.

It is true that having many columns with null values is not recommended. I would suggest you make the picture table a weak entity of user table and have an identifying relationship between the two. Picture table entries would depend on user id.

Make the profile picture a nullable field on the user table and be done with it. Sometimes people normalize just for normalization sake. Null is perfectly fine, and in DB2, NULL is a first class citizen of values with NULL being included in indices.

I agree that NULL is bad. It is not relational-database-style.

Null is avoided by introducing an extra table named UserPictureIds. It would have two columns, UserId and PictureId. If there's none, it simply would not have the respective line, while user is still there in Users table.

Edit due to peer pressure

This answer focuses not on why NULL is bad - but, on how to avoid using NULLs in your database design.

For evaluating (NULL==NULL)==(NULL!=NULL), please refer to comments and google.