How to handle optional fields in MySQL?

Question

I have a MySQL table that records classified listings. We don't force users to join to post a listing, and therefore the listing will not always have a user_id associated with it.

I therefore need a method of recording the poster's email if they are not signed in.

Is it bad practice to create a column email that will sometimes be blank and sometimes be filled?

Or is there a better way to go about this that I don't realize?

Answer 1

Is it bad practice to create a column email that will sometimes be blank and sometimes be filled?

It is not a bad practice, no : juste use a NULL column -- that's why they exist ;-)
See 12.1.17. CREATE TABLE Syntax : in the column_definition part of the create table query, you can specify NULL or NOT NULL.

BTW: Using NULL, which literally means "no value" is better than using some kind of "impossible value", like an empty string : NULL really means "no value", and make your point obvious -- while an empty string could mean an error in your code.

And I don't really see another "logical" way, actually...

Note, though, that you'll have to handle a NULL value for the email, in your application's code, of course ;-)