Page 1 of 1
Help with table design
Posted: Thu Apr 27, 2006 12:16 am
by phpwalker
I'm facing this problem, don't know how to design the tables in database.
The scenario is here, I store user details in User table. If the user is banned or deleted for some reasons, but I want to keep his information (all of his information), what should I do?
Create another table called DeletedUser/BannedUser? Update the deleted user info in table BannedUser and remove all his data in table User? Do I need to leave a username or anything in table User to link it to the DeletedUser table?
User table has link to many others table such as item, message and etc. I want to delete all his data in the following tables if the user is deleted. What concern me is I do not know how to accomplish that.
If someone want to view the deleted user info, it will going show the user has been terminated. And deleted user is not allow to view any of the protected pages in the website.
What if someone register using the name of the deleted user? Should I allow or disallow them to register the deleted user's name? I know that the email address is a must not allow. However, the name of deleted user that I use is a primary key in the database.
Anyone can guide me on the design of the table base on the above criteria?
Posted: Thu Apr 27, 2006 12:31 am
by Christopher
There are many questions in your post -- too many to answer all of them. Here are some thoughts:
- Start simple with the database schema
- Abstract the database behind a software interface so you can change the database and insulate those changes from your programmers.
- Don't use the username as the database key.
Posted: Thu Apr 27, 2006 12:32 am
by s.dot
I would have a field in your user table that would indicated whether or not that user is deleted or suspended or active.
Of course if this field was set to suspended or deleted, then you would need to update your scripts to act as if that user didn't exist.
I don't think a whole nother table is necessary.
Posted: Thu Apr 27, 2006 12:47 am
by phpwalker
Thanks for the replies.
To scottayy
A field that will contains if the user has been deleted is a good suggestion.
What about other's table that linked to the deleted user? Just delete all data in those tables, right?
I use username as primary key because it needed to be use for login. I would have to check if any member using the name before registration made. Otherwise, if I use the auto generate ID as primary key, there will be a lot of user using same name to login then. Is this good?
Now, I will check if the person is login, after that check if the person is deleted only show the content of the pw protected pages.
For registration stage, do I need to check if the username is in use including deleteduser's name as well?
Still a bit confuse, hope can guide me further.
Posted: Thu Apr 27, 2006 1:13 am
by s.dot
Using the id as a primary key is a good idea. You should check whether a username exists before allowing someone else to use it as well. The usernames should be UNIQUE, but you could also (and probably should) have the primary key as the id.
As far as the deleted issue goes, I would set your scripts up to not recognize anyone in the users table who has been flagged as deleted. This way your scripts don't interpret the data, and the user could be deleted, or "undeleted" simply by altering your one table field.
Posted: Thu Apr 27, 2006 1:18 am
by timvw
phpwalker wrote:
What about other's table that linked to the deleted user? Just delete all data in those tables, right?
You can keep the data. But anytime you select, you'll have to join with the 'main' usertable where you can lookup if he's banned or not.
phpwalker wrote:
I use username as primary key because it needed to be use for login. I would have to check if any member using the name before registration made. Otherwise, if I use the auto generate ID as primary key, there will be a lot of user using same name to login then. Is this good?
You can place a UNIQUE constraint on the username. No more problems with duplicates.
phpwalker wrote:
For registration stage, do I need to check if the username is in use including deleteduser's name as well?
Currently the username is the primary key, thus it's impossible to have a duplicate of it, even though he's deleted. (You might think: i'm smart, i'm making a primary key (username, banned) but that doesn't seem like a good idea to me) Notice that with a unique constraint on the username you can't reuse usernames neither..
Posted: Fri Apr 28, 2006 1:30 am
by phpwalker
Many thanks to
timvw and
scottayy again for helping me to solve this problems. I've been thinking of this table design for a while, now saves me from creating a duplicate table which will store the redundant data.
user table:
Code: Select all
CREATE TABLE user (
user_id int(25) NOT NULL auto_increment,
first_name varchar(25) NOT NULL default '',
last_name varchar(25) NOT NULL default '',
email_address varchar(25) NOT NULL default '',
username varchar(25) NOT NULL default '',
user_info text NOT NULL,
password varchar(255) NOT NULL default '',
user_level enum('0','1','2','3') NOT NULL default '0',
signup_date datetime NOT NULL default '0000-00-00 00:00:00',
last_login datetime NOT NULL default '0000-00-00 00:00:00',
activated enum('0','1') NOT NULL default '0',
activation_key varchar(255) NOT NULL default '',
PRIMARY KEY (user_id)
) TYPE=MyISAM COMMENT='User Information';
The user level '0' means guest, '1' means banned user, '2' means ordinary user, '3' means admin.
Any comment to my user table? Please feel free to talk about it.
Posted: Fri Apr 28, 2006 1:36 am
by feyd
Having "banned users" above "guests" feels weird.
Posted: Fri Apr 28, 2006 1:41 am
by phpwalker
feyd wrote:Having "banned users" above "guests" feels weird.
Why?
Like if a guest view protected page, it is going to ask them to login first. So his level is 0.
If a login user(or banned user), he(a banned user) can view certain pw-protected pages rather than all can't be viewed. At least I can show the banned users the page that inform them have been banned?
So, isn't if I put him as 1 better than 0?
Posted: Fri Apr 28, 2006 2:02 am
by feyd
phpwalker wrote:Why?
To myself, a banned user would have less rights to anything and everything than anyone else, guests included. Granted, guests and banned users would have very similar rights. Technically, probably the same, except the messages one certain pages would change from "please log in" to "you are not allowed access," but that's a very small difference.