Database design: users and guests

Question

I have two types of people on my site, users and guests. Virtually they are the same, except for creation/auth processes.

At the moment I have two tables:

t_users

userId[PRIMARY, AUTOINC] username[UNIQUE]

t_guests

guestId[PRIMARY, AUTOINC] userId

When somebody enters the site, script does the following:

1) creates new guest record by inserting a new row to t_guests

2) adds new record to t_users, using guestId generated on previous step (guest username = “Guest guestId”)

3) updates guest record setting userId assigned on step 2

I feel this database design to be just awful, because it contains many vulnerabilities. For example, if username "Guest xyz" already exists in t_users table, step 2 will fail and step 3 will assign wrong value to the userId (depending on implementation it’ll be 0 or guestId, assigned on step 1).

Actually I only need t_guests table for its auto increment feature to generate unique usernames for guests. Is there a way to use just one consolidated table and register guests using single query?

UPDATE: I can do the following to insert guests in a single table:

SELECT @mg := IFNULL(MAX(guestId), 0) + 1 FROM t_users;

INSERT INTO t_users (guestId) VALUES(@mg);

But I can't be sure, that nobody inserts a new guest record in t_users between execution of those two statements. And I can't make guestId unique, because real users will have it equal to zero.

Answer 1

If you just have 1 table with" userID, username, type

for the username you could use your script to generate a Guid and use that as the username, or someother random variable. If you use a GUID it is virtually impossible that you get 2 guids that collide.

Also if you do have 2 usernames that collide if you make sure that the username column has to be unique then the insert will fail and you could just try again.

You definitely aught to just have 1 table here.