使用两个表，如一个表

Question

I have two different type users table. One of them is ordinary user table for registered users. Other one is for unregistered users. I need to use these two tables with other tables. For example;

registered_users: userid, username, password, name, sex, email, ip

unregistered_users: userid, name, sex, ip

some_table: id, title, content, userid

How can I join some_table with user table?

I have some oppinion but none of them does not feel right.

1. use all table regUser column and unRegUser column with null values and join two table etc. This way non suitable because all tables have lots of null cell.
2. if all table like some_table add a column userType and each query has "if". But this way is long and spends resources.
3. if userid column type will be string (r13, u32 - r:registered, u:unregistered) and use "substring" and "if" but this is more difficult
4. create a connection table like "id, type, userid" and use this id instead of userid. but also need "if"
5. create a connection table like "id, regUser, unRegUser" with null values.

Or create a different user table to be combine of two user tables. Of course we have null columns again.

You think which way is the best? Or any one has different opinion.

Answer 1

When contents are same why do you try to duplicate it by creating two tables instead you can create a single table called users

create table users
(
user_id int not null auto_increment primary key
username varchar(500),
password varchar(500),
email  varchar(400),
ip     varchar(800),
reg_status enum(0,1),
);

then you can use this user_id and reference it your secondary table.

You can build up your query on reg_status

0 - Unregisterd
1 - Registered

If you want more normalized table

then you can use two tables

create table users
(
user_id int not null auto_increment primary key
email  varchar(400),
ip     varchar(800),
reg_status enum(0,1),
);


create table registered_users
(
login_id int not null auto_increment primary key
username varchar(500),
password varchar(500),
user_id - F.k (Foreign Key)
);