Database design with different users

Your rough idea is heavily normalized: I am a fan of heavy normalization. However, I would use the user type as a lookup table, and not separate tables for each type. This is roughly how I've done it in the past, it acheived what I needed (written in PostGreSQL). I wouldn't recommend using my code directly without applying more thought to it, I am just conveying a concept I have used before.

---create the users table

CREATE SEQUENCE users_id_seq;
CREATE TABLE users (
id                integer NOT NULL DEFAULT nextval('users_id_seq'),
email           character varying(100) NOT NULL,
username    character varying(100) NOT NULL,
password     character varying(255) NOT NULL,
usertypeid    integer NOT NULL,
CONSTRAINT users_pkey PRIMARY KEY (id)
);
ALTER SEQUENCE users_id_seq OWNED BY users.id;

--create the index for the foreign key
CREATE INDEX fki_users_usertypes_fkey1
    ON users
    USING btree (usertype);

---create the usertypes table, you will insert rows such as 'buyer', 'seller', 'admin', etc.
--make the table wider if you are trying to store more information
CREATE SEQUENCE usertypes_id_seq;
CREATE TABLE usertypes (
id             integer NOT NULL DEFAULT nextval('usertypes_id_seq'),
usertype   character varying(25) NOT NULL,
CONSTRAINT usertypes_pkey PRIMARY KEY (id)
);
ALTER SEQUENCE usertypes_id_seq OWNED BY usertypes.id;


--create the foreign key
ALTER TABLE users
ADD CONSTRAINT users_usertypes_fkey1 FOREIGN KEY (usertypeid)
    REFERENCES usertypes (id) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION;

So in a nutshell, make the users table have a usertype integer column to hold a value from the usertypes lookup table. Make the usertypes table as wide as you need it. If you want to implement permissions from the database, I would NOT recommend [in this example] to put them in the usertypes table.

In your User model, you can something like this to return their usertype (assuming that you also have made a UserType model).

public function userType() {
     return UserType::find($this->usertypeid);
}

Let me know your thoughts...

Moderators