Help with table design

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
phpwalker
Forum Commoner
Posts: 81
Joined: Sun Apr 23, 2006 12:18 pm

Help with table design

Post 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?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post 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.
(#10850)
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
phpwalker
Forum Commoner
Posts: 81
Joined: Sun Apr 23, 2006 12:18 pm

Post 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.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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..
phpwalker
Forum Commoner
Posts: 81
Joined: Sun Apr 23, 2006 12:18 pm

Post 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.
Last edited by phpwalker on Fri Apr 28, 2006 1:38 am, edited 2 times in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Having "banned users" above "guests" feels weird.
phpwalker
Forum Commoner
Posts: 81
Joined: Sun Apr 23, 2006 12:18 pm

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Post Reply