Yii Framework Forum: Correct Database Setup to work properly with user roles - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Correct Database Setup to work properly with user roles How to store very different user types in database

#1 User is offline   pmg 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 3
  • Joined: 12-November 17

Posted 16 November 2017 - 02:33 PM

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.yiiframew...horization.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
0

#2 User is offline   samdark 

  • Having fun
  • Yii
  • Group: Yii Dev Team
  • Posts: 5,193
  • Joined: 17-January 09
  • Location:Russia

Posted 17 November 2017 - 05:00 AM

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.
Yii 2.0 Development Cookbook

Enjoying Yii? Star us at github

Support me so I can work more on Yii: https://www.patreon.com/samdark
0

#3 User is offline   alrazi 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,496
  • Joined: 08-August 12
  • Location:Durban, South Africa

Posted 17 November 2017 - 06:06 AM

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.


Quote

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.


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
1

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users