Re: Table Structure

Peter, sorry, I should haven mentioned that the client - rep relationaship is one to many. A client will have many reps working for them, but only staff, administrators and reps can log. This system works fine at the moment but i want the system to beflexible in case i need to add another user type... "Peter Beckman" <beckman@purplecow.com> wrote in message news:20030508140821.E2026@thermonuclear.org... > Why not combine them: > > userid > username > password > name > type > email > location > usertype enum("admin","staff","rep") > primary key (userid) > > No sense in wasting tables when you are repeating data. And this way it's > easier to do authentication. > > Since you didn't say that a rep can also be a staff or admin, I'm assuming > they can't. If they can, I suggest a unix file permission type solution: > > 1 = staff > 2 = admin > 4 = rep > > usertype tinyint > > usertype 1 staff > usertype 2 admin > usertype 3 staff, admin > usertype 4 rep > usertype 5 staff, rep > usertype 6 admin, rep > usertype 7 staff, admin, rep > > Peter > > On Thu, 8 May 2003, shaun wrote: > > > Hi, > > > > I have the following tables in my database. A user could be of type Staff, > > Client or Administrators. A client could have many reps. Only staff, > > administrators and reps can log in to the system. I would appreciate your > > comments on this model. Have i modelled this correctly, or should i have a > > seperate table for each user? > > > > Thanks for your help > > > > > > # ---------- MySQL dump ---------- > > # > > # Table structure for table 'WMS_Rep' > > # > > CREATE TABLE WMS_Rep ( > > Rep_ID int(11) DEFAULT '' NOT NULL auto_increment, > > User_ID int(11) DEFAULT '0' NOT NULL , > > Rep_Name varchar(40) DEFAULT '' NOT NULL , > > Rep_Username varchar(20) DEFAULT '' NOT NULL , > > Rep_Password varchar(20) DEFAULT '' NOT NULL , > > Rep_Location varchar(40) DEFAULT '' NOT NULL , > > PRIMARY KEY (Rep_ID,User_ID) > > ); > > > > # > > # Table structure for table 'WMS_User' > > # > > CREATE TABLE WMS_User ( > > User_ID int(11) DEFAULT '' NOT NULL auto_increment, > > User_Username varchar(20) DEFAULT '' NOT NULL , > > User_Password varchar(20) DEFAULT '' NOT NULL , > > User_Name varchar(100) DEFAULT '' NOT NULL , > > User_Type int(11) DEFAULT '1' NOT NULL , > > User_Email varchar(70) , > > User_Location varchar(40) , > > PRIMARY KEY (User_ID) > > ); > > > > # ----------- Dump ends ----------- > > > > > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > -------------------------------------------------------------------------- - > Peter Beckman Internet Guy > beckman@purplecow.com http://www.purplecow.com/ > -------------------------------------------------------------------------- -