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...
However, the above design does not permit users to have more than one user type. That is up to you if you want to change that.
Sign in to participate in this thread!
The Laravel portal for problem solving, knowledge sharing and community building.
The community