How to handle optional fields in MySQL?

Question

I have a MySQL table that writes classified lists. We do not force users to join the publication, so the list will not always be linked to user_id.

So I need a way to record by email if they are not logged in.

Is it a bad practice to create a column emailthat is sometimes empty and sometimes filled?

Or is there a better way to do this that I don't understand?

Answer 1

Is it a bad practice to create an email column that will sometimes be empty and sometimes fill out?

, : juste NULL - ;-)
. 12.1.17. CREATE TABLE : column_definition create table NULL NOT NULL.

BTW: NULL, " ", , - " ", : NULL " " - .

"" , ...

, , , NULL , , -)

Answer 2

, NULL. , , user_id NULL, ?

Answer 3

, . Longneck, , NULL SQL- .

, , (, , ), user_id userid , , , is_email, , user_id. , , , , user_id .

Answer 4

MySQL, . , user_id.

, .

- ? , : ? - , ? : RegisteredUser UnknownPosterWithEmailAddress, ?

NULLable user_id NULLable , , , " , , ", , :

select post.id, 
   case when post.user_id is not null then user.email
        else post.email end as email
   from post
   left join user on user.id=post.user_id;

.

: User dsitinct , NULLable, :

create table user(id integer primary key,
                  email text not null unique,
                  is_registered boolean default false);
create table post(id integer primary key,
                  user_id integer not null references user(id),
                  content text);

, user.id, . , : , ? , NULLable.

, , ( user.id , , is_registered ). :

• If he sent it to the same email address, now all his old messages are automatically associated with his new registered identifier.
• If a user changes his email address in his profile, all replies to older messages will be "seen" by his new updated email address.