1

I am looking for some general information in regards to User Table Design.

I have an old table design for 'users', which I need to update but not breaking the entire site's structure.

Current Table Design

UserID | Email   | FirstName | Last Name | ...
1      | a@a.com | John      | Doe       | ...
2      | b@b.com | Jane      | Doe       | ...

I need to be able to create "Primary" users, as well as "Assitant" users.

Now I believe I should have a few tables designed:

  1. Users
  2. Accounts
  3. Users > Accounts - (Relationships & Permissions) IE: of users > accounts

TableID | UserID | AccountID | PERM 1 | 1 | 1 | 001

So I guess my question is. Is there a better way to do this? Specifically if there is a current design being used?

Hope this makes sense. Any direction in this would be greatly appreciated.

Justin
  • 2,502
  • 7
  • 42
  • 77
  • 1
    Maybe classify your users with a `group_id` that references a `groups` table. I assume the purpose of this is to use different permission sets for different "kinds" of users. – Scotty C. Oct 03 '15 at 00:36
  • 1
    On an unrelated side note, it's possible to put your database queries in their own functions, such as `saveUser($name, $email, $firstName, $lastName)`. If you keep all database queries separate from the business logic, it's much easier to change them later, and "breaking entire site's structure" is less of a threat. – Scotty C. Oct 03 '15 at 00:40
  • @ScottyC. Yeah groups is a decent way to put it. Easy enough to just add a "Groups" Table. I think the problem I might be having currently, is. If I have a primary user login, and then add an "Assistant" login, making the same 'data' available. LoggedIn "ID" is different from the primary user... they almost need to share that ID. Just because of the current architecture.... hard to 'share' IDs... thoughts on that? – Justin Oct 03 '15 at 00:45
  • Well, the easiest solution would be to add two "boolean" columns to the `users` table, one indicating the user is `primary`, and the other indicating `assistant`. Setting either or both to `1` says they belong to that group. This will blow up if end up with 56 groups in the future. – Scotty C. Oct 03 '15 at 01:15
  • I see no point in the adding of columns and adding of tables for each group. That is madness. You need a junction table sort of design – Drew Oct 03 '15 at 03:16
  • @Drew do you have a sample of that concept. I believe I have an idea in my head. Just want clarification if possible. – Justin Oct 03 '15 at 05:28
  • 1
    I hesitate because you say do not want to break the whole sites structure. That is why I did not make an Answer. But, if you see my answer [here](http://stackoverflow.com/a/32620163), and keep in mind that CSV in a column is so similar to 'adding a column for each role', then you will be able to easily overlook the CSV verbiage in that answer over there and just focus on the two tables with an intersect junction table – Drew Oct 03 '15 at 11:46
  • So for you, you have User, Role, and a Junction. Over there, it was Student, Course, and a Junction. If you ponder it and want me to write up a complete Answer then supply the table schemas and concepts, and I will. But I think you can take it from here. Good luck – Drew Oct 03 '15 at 11:48

1 Answers1

1

Here's an example where you'd have a table for each group, plus a users table. You can filter the users by group using a JOIN. Personally I don't love this. If anyone else has a better suggestion, I'd like to hear it.

http://sqlfiddle.com/#!9/993dd/1

Scotty C.
  • 744
  • 4
  • 16
  • Hey thanks for the sample. In your previous comment, why exactly wuold it blow up at 56 groups... as the group 'table' wouldn't have a problem going larger. This is the 'general' concept that I was thinking about. But not sure the ramifications, if any.. – Justin Oct 03 '15 at 02:40
  • In my last comment I was talking about adding a new column to the users table for every group, indicating if they belonged to it. That would get ugly quick. Like: `| primary:0 | assistant:0 | supervisor:0 | etc.. | email | fist_name | last_name |` – Scotty C. Oct 03 '15 at 06:57
  • Yeah I agree, I think using the seperate table for permissions / roles is the way to go in this scenario... a mix between Yours and @Drew comment will work just fine i'm sure. I think the next step is just as difficult. Showing the proper content based on permissions / grouping. The joys of an old mixing in the new. – Justin Oct 05 '15 at 17:44