So add one additional field to that single table: parent int default null Every rep that has a client as a parent, their ID will go into that field. If the parent is null (or you could declare it not null and save a bit and check to see if it is 0) then that person is probably not a rep. Peter On Thu, 8 May 2003, Shaun wrote: > 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 > 20030508140821.E2026@thermonuclear.org">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/ > > -------------------------------------------------------------------------- > - > > > > -- > 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/ --------------------------------------------------------------------------- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
