Correct Database Setup to work properly with user roles

General Architecture, design, OOP
1

Hey there,

in my project, I will have 3 quite different user types which may login and use different functions.

I already found this nice article about the Yii side of this:

http://www.yiiframework.com/doc-2.0/guide-security-authorization.html

My question is how to best design the database to store different user types so that the role management works best with Yii.

I will have 3 user types: admins, guides and partners. Each of them will have different attributes, at least these:

admins (less than 10 admins in total):

id

firstname

lastname

username

details

password

-> n:m relations to phone, email and address tables

guides (less than 100 guides in total):

id

firstname

lastname

nickname

username

password

status

details

gender

choice

birthday

-> n:m relations to phone, email, address and tour tables

partners(less than 100 partners in total):

id

name

firstname

lastname

username

password

status

commission

details

-> n:m relations to phone, email, address and group tables

At the moment I can think of 3 ways to store these 3 user types:

  1. Store all of them in seperate tables

  2. Store all of them in one table making some columns empty often

  3. Store the common attributes in one table (id, firstname, lastname, username, password) and store the different attributes in extra tables.

From a general and a Yii point of view, whats the best way to achieve what I want? Thanks a lot for your help!

Philipp

2

These three are quite similar so I’d merge them into one “user” table. Unique fields could be in separate 1:1 table or stored in the same table. Separate table is more correct. Same table is a bit simpler.

3

It really depends on your application as well as the domain, there are certain question you should ask before you make major changes in your design. I always start small and continue building on as time goes, thinking about future changes is logical at the same time you need to weight how much more time and effort you going to need to make your app future proof.

I think any one of the above could be a solution, but I think 3rd one gives you more flexibility in terms of future changes you can always add more fields on the fly without duplicating.

users table have a very basic and limited set of fields which are common/shared across all 3 types.

users

  • email

  • username

  • password

  • active

  • timestamps

you could move your additional fields to a seperate table which can be used for

your n:m fields as well as the unique ones perhaps called fields/attributes.

for example user type admin with id 1 might have firstname,lastname,birthday which can be represented by another link table like so

user_field

1, 1

1, 2

1, 7

fields

  • firstname

  • lastname

  • nickname

  • details

  • gender

  • choice

  • birthday

  • comission

  • phone

  • phone 2

  • phone 3

field values can be stored in a separate table which you can retrieve based on the users -> fields.

field_values

user_id,

field_id,

value