Please review my DB design

one issue that jumps out is that as a user, I might be an admin but only for one application. Maybe the 'teams' need to be application specific. How can I be a member of the Jobs but an admin of the forum?

These are more logically close to roles rather than team and you might want to put an app_id column in that table?

Yeah, in fact I changed to 'roles'. Teams turned out to be complicated when it came to naming stuff.

one issue that jumps out is that as a user, I might be an admin but only for one >>application. Maybe the 'teams' need to be application specific. How can I be a >>member of the Jobs but an admin of the forum?

Any registered user will have the member role, sitewide. Jobs app will check if the user has 'member' role and let them perform allowed actions.

We'll need special checks only when user is accessing special areas. For example, the super admin can make any member an admin for the forum by granting them ForumAdmin role.

Perhaps, I'll have to prefix the roles with app names.

Does that look right? Or is there a better approach?

city | state | country

Could each be in it's separate Table: cities, states, countries. where:

• City belongsTo State
• State belongsTo Country

You then only give a city_id to the users table, and you do not have duplicate Content.

With 1000 users / day or more, the Users Table Size might grow a lot if you keep it like it is.

@xmarks - Yes, that's another normalization I might do. Maybe I could have a user_meta table :

id | user_id | meta_key | meta_value 

1   |     1         | city             | Panama City
2  |     1         | country     | Republic of Panama
3  |     1         | state           | Panama State
4  |    2         | country     | India

and so on. I'm assuming that the table size should not be a problem at all for databases. Or should it be a matter of concern?

I'm wondering if there's any advantage of storing data like that as compared to adding more columns on the users table.

Umm.. not really. I mean I would say splitting something from 1-table into 2-tables, and have the same data anyway, you end up actually with more data. As you have to specify a meta_key field, and the meta_value is again duplicated.

On your example just now you have duplicates:

• user_id => shown 3 times => 3(city, state, country) x 4bytes(Int) = 12 bytes (for each user)
• meta_key 'city' => let's say 1000 users are from same city => 1000 x 1 byte(varchar 255) => 1000 bytes ~ 1MB
• for a specific city you also have a state and country so you end up with ~ 3 MB for 1000 users.

So the growth of your user_meta table is now virtually Infinite

What I meant is, you should split the data so there are no Duplicates. In your case above:

• users => id | email | first_name | last_name | phone | city_id
• cities => id | name | state_id
• states => id | name | country_id
• countries => id | name

The growth of these 3 tables is Finite. There is a limit to Cities, States and Countries. And you just connect this data to the users table as needed through city_id. You are not even required to specify state and country as they are related to cities and thus you can access them through that.

Now, if you want to access the data, after you have defined the relations:

• user->city->name
• user->city->state->name
• user->city->state->country->name

You can also use the city_id, on any other tables you may have as well, as this is simply data which you can connect and reuse elsewhere.

There is no reason to split data, like phone from users table. The drawback of having a lot of data on users table is when you select all() fields. Like: User::all(); But if this is getting out of hand (if you have hundreds of columns) then you can just select them specifically like User::select('first_name', 'last_name', 'email')->get()