Level 21
Anyone? Any Ideas? Would be a great help!
Mar 20, 2015
13
Level 21
[DB] Is it a good database design to implement?
This is not a Laravel relationquestion, This is a database related question. Because there is quite very advanced developers here, I am posting this question here.
I have a database design, but I'm not quite sure if this design is good for implementation. The design is as follows: (showing only related columns, there are several other columns in few table)
Approach A:
Table 1: Roles
id
role
Table 2: A_Users
id
email
role_id // reference ID on roles table
...
Table 3: B_Users
id
email
role_id //reference ID on roles table
...
Table 4: passwords
user_id // references ID on A_Users and B_Users Table. (not a foreign key, just and index)
password
remember_token
View 1 : users
id // references user_id from passwords tTble
email // references email from A_Users and B_Usres Tables
password // references password from Passwords Table
I can not merge A_users and B_users as they have different columns.
I have another approach which is:
Approach B:
Table 1: roles
id
role
Table 2: users
id
email
password
role_id //references id on roles table
...
Table 3: A_Users
id // references id on users table
...
Table 4: B_Users
id // references id on users table
view 1: A_Users_profile
id // references ID on A_users table
email // references email on users table
... //rest all from A_Users table
view 2: B_Users_profile
id // references ID on B_users table
email // references email on users table
... //rest all from B_Users table
The problem is, when B_Users are admins and A_Users are normal users.
So when A_Users register, they do not get profile instantly. B_users first verify the details and when they Approve, The password will be auto generated and emailed to A_users.
Now when A_users register, I have to store their email address in master table instead of Users (LogIn) table.
But then adding their email to Users table after approving will cause data repetition in 2 tables. So I came up above 2 approaches.
So, My question is Which approach would be a better one? As i'm not able to decide. Both seems to be equal to me.
And If none then, how can i implement this?
Thanks In Advance. :)
Level 9
Users are users no matter what. If you need to approve some users you can create a table for requests (access request, just think for better name) in approval section you just show that requests, and when people approve it then you create user and email him information about his user. Just users must contain all users. Roles will decide who is who (admin, moderator, user, validated, banned) or w/e else role people have. If you need just one role you can set role_id under users table. If your user can have multiple roles then you create a pivot table between users and roles (role_user:user_id, role_id) That means your user_profiles table will be united as well.
In otherhand you can just put requests into users table under role of (validating) and then approve or delete this role type users. Password set to random hash so nobody can guess that. And when user is approved you set him a password or make another hashed password, change his password and email the hash.
Just to make sure everything you need is actually 3 (or 4 if user can have multiple roles)
- users
- user_profiles
- roles
- role_user (optional)
Level 7
I would say there is no need for two users table even if they have different columns.
Normalize your tables and use polymorphic relations. To reference different tables with a foreign key on your users table. If there is a need for it.
Alex
Level 88
Why don't u use polymorphic relations http://laravel.com/docs/5.0/eloquent#polymorphic-relations
Level 21
@kemito The prob is, i have different columns in different users table.
@alexwolff I was trying to maintain a master table for users, where i can have all the data of user in one table, and create a view for most accessible columns. Wouldn't be better to have master table? without master table I can go with your approach, that is what i used to do in past. But then Master table concept seems to be more better to me.
@blackbird i have seen it, but trying without relation, because, with relation, it will be one extra query. (not N+1). I can do with custom join query but when i have views then why should i execute the joins every time? A view will do it for me anyways right?
Level 21
Guys? Some Help Here?
Level 5
I'm not sure I understood your problem entirely, but I saw this article re Party Model on @JarekTkaczyk 's site the other day and maybe it has similar concept and helpful ideas:
Level 21
@londoh That comes to same point, I will have to implement polymorphic relation. When i can crate a view and can get the result with just one query to the view, y should i use relations to execute multiple queries?
Level 88
That's not the point. We tell you to use polymorphic relations, that doesn't mean you need more queries!
You can just create your own query with joins and stuff to get the result, but your database structure is set up to use polymorphic relations. It's the easiest way to go about storing data with the links between your tables.
Level 21
@blackbird Ok I got that point. But as i said, i am trying to maintain Master table, And when A_user register, all the details will be going to A_users table. But when the A_user is approved, it will have entry of email and password in Users table. (i hope u got what i am trying to say.) So I want to know how can i design my database in best way for this scenario? If i cope email in Users table it will be like data duplication. So what could be the other way. And about polymorphic relation, my view will be polymorphic with A_users and B_users anyways. I want is database structure. Can you help me with that.?
Level 88
What you can do is connect the users table to that A_user and B_user table you are talking about
users:
- email
- password
- remember_token
A_user:
- user_id
- name
- address
- country
B_user:
- user_id
- something
- something_else
- another_thing
This way you don't have to define the users email twice and you can still keep the regular authentication stuff
Level 21
@blackbird Ya the 1st thing came to me was this only. But was trying if i can keep email in respective tables and still can make users table, so to retrieve email, i can stick to same convention i.e.,
$user->A_users->email;
and not
$user->email
because i will be getting other details from A_Users so it will maintain readability. Seems like i have to stick with this structure only.
Level 53
@blackbird Your last suggestion won't work at all, but you can map email like here
https://laracasts.com/discuss/channels/general-discussion/map-relations-field-directly-on-the-model
and using for example https://github.com/jarektkaczyk/eloquence
And remember $this->relation()->whatever_property won't work, you need to call the dynamic property $this->relation->...
Please or to participate in this conversation.