MYSQL - one column per piece of data or combine into one column

Question

I'm creating a blog, and am storing user permissions (to post/edit/delete blog posts) in a mysql table.

Should I create one column per permission, or combine all percussions into a string in one column such as 101 would mean that a user could post and delete but not edit.

The reason I ask is that I am worried about having too many column in my table.

Answer 1

My choice would be separate columns. Makes it easier to query later on if you are looking for specific permissions.

You might want to check out some standard designs on permissions, no need to invent the wheel for the 4th time :)

Answer 2

Consider mysql's (nonstandard) SET type. More experienced coders may favor a bit field (which is really what's underneath mysql's SET type).

Don't use a string because:

1. A string is a very inefficient way to store bit values -- you're using a byte per flag, where you only need a single bit
2. Querying against that field would require heinous string manipulations, and would never be efficient

Answer 3

You could use bitwise combinations (bit fields) within one column like

const READ = 1;
const WRITE = 2;
const DELETE = 4;
...

so resulting permission would be

read-only: 1
read-write: 3
read & delete, but not write: 5
and so on...

To check the permission in a bit field, your query has to look like

SELECT * FROM table t WHERE t.permission & required_permission

with required_permission being the bitwise or of the required permission flags.

But I also would recommend to check out some resources to find out about standard designs...

Answer 4

I'd say it would be fine to put a Post, Edit and Delete Column.

But, if you take Wordpress's take on permissions, they simply serialize it into an array, and then store that array in a table of Settings (4 Columns: UserID, Settings Key, Setting Value). I think Wordpress's method only really works if you aren't going to give permissions their own table.

Another method is to do a User_ID - Permission Relationship Table. In one column put the User_ID, and in the other the permission. But, make each row a permissions. IE, if you wanted to give User ID 1 all permissions it would be:

Permissions: Add: 1, Edit: 2, Delete: 3

Table

Row 1: UserID: 1 Permission: 1

Row 2: UserID: 1 Permission: 2

Row 3: UserID: 1 Permission: 3

Answer 5

First of all, I would rule out combining all permissions into a single field. It seems economical at first, but it can turn into a bit of a problem if you will ever need to expand or modify your permissions structure.

Creating a column for each permission in the user table is a good design for a simple system, but may limit your future expandability.

I recommend implementing a many-to-many relationship between users and permissions. This allows you to add as many types of permissions you want without changing the schema. It is very easy to query with a simple join, and is portable to other databases.

You accomplish this by creating two new tables. Assuming the following schema:

CREATE TABLE `users` (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(100),
    -- other user fields --
);

We can add the m2m permissions schema like this:

CREATE TABLE `permissions` (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL UNIQUE,
);

CREATE TABLE `users_permissions` (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY_KEY,
    `user_id` INT NOT NULL,
    `permission_id` INT NOT NULL 
);

You might then add some sample users and permissions:

INSERT INTO `users` (DEFAULT, 'joe');
INSERT INTO `users` (DEFAULT, 'beth');
INSERT INTO `users` (DEFAULT, 'frank');

INSERT INTO `permissions` (DEFAULT, 'Administrator');
INSERT INTO `permissions` (DEFAULT, 'Write Blog');
INSERT INTO `permissions` (DEFAULT, 'Edit Blog');
INSERT INTO `permissions` (DEFAULT, 'Delete Blog');

And finally you can associate users with permissions like so:

-- joe gets all permissions
INSERT INTO `permissions` (DEFAULT, 1, 1);
INSERT INTO `permissions` (DEFAULT, 1, 2);
INSERT INTO `permissions` (DEFAULT, 1, 3);
INSERT INTO `permissions` (DEFAULT, 1, 4);

-- beth can write and edit
INSERT INTO `permissions` (DEFAULT, 2, 2);
INSERT INTO `permissions` (DEFAULT, 2, 3);

-- frank can only write
INSERT INTO `permissions` (DEFAULT, 3, 2);

For a smaller blog, you may not need a flexible schema like this, but it is a proven design. If you like, you can also take this one step further and create a role system. This works by giving each user a role (one-to-many), and each role has a number of permissions (many-to-many). This way permissions don't need to be set on a per-user basis, and you can simply assign them a role like "Administrator", or "Editor" or "Contributor", along with the associated permissions for that role.