I am creating a User Management System and need your advice.
3 entities:
- Users
- Firm
- 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;