0

I am creating a User Management System and need your advice.

3 entities:

  1. Users
  2. Firm
  3. Group

Each user will belong to one Firm. Some users (not all) may belong to Group. Group is a combination of Firms. (E.g. 3 firms in a group)

The data will be displayed according to user's firm or group, if a user belongs to firm (but not group), then firm level data will be displayed. And if the user belong to group then group level data (e.g. 3 firms' data) will be displayed.

Is my below mentioned approach correct for this purpose:

CREATE TABLE `user_account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firm` varchar(45) DEFAULT NULL, # each user belong to a client firm
  `group` varchar(45) DEFAULT NULL, # some users may belong to client group
  `name` varchar(45) DEFAULT NULL, 
  `username` varchar(15) NOT NULL, # USER ID
  `password` varchar(255) NOT NULL,
  `email` varchar(40) NOT NULL,
  `account_creation_date` datetime DEFAULT NULL,
  `active` tinyint(1) DEFAULT '1',
  `token_code` varchar(255) DEFAULT NULL, # for password recovery
  `token_creation_date` datetime DEFAULT NULL, # for password recovery
  PRIMARY KEY (`id`),
  UNIQUE KEY `username_UNIQUE` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `permission` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL, # FOREIGN KEY user_account.user_name
  `association` varchar(45) DEFAULT NULL, # Firm or Group
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Fahad Khan
  • 67
  • 1
  • 9
  • 2
    It's not how I'd do it. Users belong to firms. Firms belong to groups. Can a firm belong to more than one group? And note the group is a reserved word, which makes it a infuriating choice for a table/column identifier – Strawberry Jul 23 '17 at 09:44
  • You're probably wise to design this using entities (people, firms, groups) and relationships. You can read about that. You may find the MySQL workbench to be a useful design tool. **Pro tip** you can't be too careful when desiging your tables. Every minute of effort you take getting a good design will save you hours when programming. – O. Jones Jul 23 '17 at 10:46
  • @Strawberry you're right about `group`. The schema I'm working with these days has a table with that name. Drives me crazy. *Almost* crazy enough to go all Hungarian calling it `group_t` . – O. Jones Jul 23 '17 at 10:48
  • @Strawberry Every firm belongs to certain Group but one firm can not belong to more than one group. If this is not how you'd do it then what's the best approach in your opinion? – Fahad Khan Jul 23 '17 at 11:14
  • Essentially: A table of users and a table of firms. – Strawberry Jul 23 '17 at 11:27
  • @Strawberry Is that all? Does that mean I don't need `permission` table? Do I need a table for `group` as well? – Fahad Khan Jul 23 '17 at 11:33
  • Some of the prefixes have been omitted for the sake of privacy, so `group` column is not really just `group`. – Fahad Khan Jul 23 '17 at 11:34

0 Answers0