forkfork February 2016

PostgreSQL - Where to store owners data?

It is ok to store the owners (the ones who access the main project dashboards) in the same database schema where normal users are stored? If so, how it is best: in the same "users" table with a flag "role" or in an independent table "owners".

CREATE TABLE users (
    id BIGSERIAL NOT NULL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE owners (
    id BIGSERIAL NOT NULL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

or

CREATE TABLE users (
    id BIGSERIAL NOT NULL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    role INT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Answers


stb February 2016

I recommend to store the users in one table.

Here is why:

  • One table storing very similar kind of data
  • It's easier to upgrade a user to a superuser
  • Maintenance
  • Follow up sql statements using users will be less complex


ikusimakusi February 2016

I would say that having only one table is easier to maintain and simplifies the logic of the app. The "role" field allows future expansion in case you'd like to add new role types. Security-wise, having one or two tables should be very similar. But it depends of how you access the table, permissions, ...
I'm not sure this answer is very helpful, but we would need a bit more of context to be able to help you further...

Post Status

Asked in February 2016
Viewed 3,113 times
Voted 7
Answered 2 times

Search




Leave an answer