Support the ongoing development of Laravel.io →
Database Eloquent
Last updated 2 years ago.
0

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

Last updated 2 years ago.
0

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.

Last updated 2 years ago.
0

Sign in to participate in this thread!

Eventy

Your banner here too?

svanteh svanteh Joined 16 Oct 2014

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2024 Laravel.io - All rights reserved.