MySQL JOIN Three Tables Using Row Values of A Table

Question

This got complicated really quickly and I'm beginning to question the database design. The basic concept of the application is:

1. User accounts
2. Features
3. Access levels

So, users have different access levels for each of the features. Fairly basic and common application I would think.

Schema:

CREATE TABLE `user_accounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_login` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `user_password` varchar(60) COLLATE utf8_unicode_ci NOT NULL,
  `user_fname` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `user_lname` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `user_group` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Default',
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_login` (`user_login`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

INSERT INTO `user_accounts` VALUES(1, 'email@example.com', 'secret', 'Example', 'Name', 'Admin');
INSERT INTO `user_accounts` VALUES(2, 'john@example.com', 'secret', 'John', 'Doe', 'Trainer');
INSERT INTO `user_accounts` VALUES(3, 'jane@example.com', 'secret', 'Jane', 'Doe', 'Default');

CREATE TABLE `user_access_meta` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `type` (`type`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `user_access_meta` VALUES(1, 'type_1');
INSERT INTO `user_access_meta` VALUES(2, 'type_2');
INSERT INTO `user_access_meta` VALUES(3, 'type_3');
INSERT INTO `user_access_meta` VALUES(4, 'type_4');
INSERT INTO `user_access_meta` VALUES(5, 'type_5');
INSERT INTO `user_access_meta` VALUES(6, 'type_6');

CREATE TABLE `user_access_levels` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_login` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `type` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `level` int(1) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_login_2` (`user_login`,`type`),
  KEY `user_login` (`user_login`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

INSERT INTO `user_access_levels` VALUES(1, 'email@example.com', 'type_1', 1);
INSERT INTO `user_access_levels` VALUES(2, 'email@example.com', 'type_2', 1);
INSERT INTO `user_access_levels` VALUES(3, 'email@example.com', 'type_3', 0);
INSERT INTO `user_access_levels` VALUES(4, 'email@example.com', 'type_5', 2);
INSERT INTO `user_access_levels` VALUES(5, 'john@example.com', 'type_2', 1);
INSERT INTO `user_access_levels` VALUES(6, 'john@example.com', 'type_3', 1);
INSERT INTO `user_access_levels` VALUES(7, 'john@example.com', 'type_5', 3);
INSERT INTO `user_access_levels` VALUES(8, 'jane@example.com', 'type_4', 1);

These tables actually have a lot more fields and have foreign key constraints between them, but I've striped them down for this example. They are also used individually for other purposes.

I've successfully been able to join all three tables together for a single user with this:

SELECT
ua.`user_fname`,
uam.`type`,
ual.`level`
FROM `user_access_meta` uam
LEFT JOIN `user_access_levels` ual
ON ual.`user_login` = 'email@example.com'
AND uam.`type` = ual.`type`
JOIN `user_accounts` ua
ON ua.`user_login` = 'email@example.com';

Output:

| USER_FNAME |   TYPE |  LEVEL |
--------------------------------
|    Example | type_1 |      1 |
|    Example | type_2 |      1 |
|    Example | type_3 |      0 |
|    Example | type_4 | (null) |
|    Example | type_5 |      2 |
|    Example | type_6 | (null) |

Even this isn't ideal, but It's all I could come up with and it serves it's purpose.

---

Now, what I need to do is select all users including their access levels. It would look something like this:

| USER_FNAME |  type_1 |  type_2 |  type_3 |  type_4 |  type_5 |  type_6 |
--------------------------------------------------------------------------
|    Example |       1 |       1 |       0 |  (null) |       2 |  (null) |
|       John |  (null) |       1 |       1 |  (null) |       3 |  (null) |
|       Jane |  (null) |  (null) |  (null) |       1 |  (null) |  (null) |

I feel this may not have been the best design, but the reason I went with this design is so that I can easily add and remove features or even temporarily disable them individually.

Should the design be rethought? Is it even possible to get the results I'm looking for with this design?

I've put this up on SQL Fiddle. http://sqlfiddle.com/#!2/bb313/2/0

Answer 1

I have a few suggestions on both your table design and then how to get the data in the format that you want.

First on the database design, the change I would advise is in the table user_access_levels. Alter you table to the following:

CREATE TABLE `user_access_levels` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `type_id` int(11) NOT NULL,
  `level` int(1) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id_2` (`user_id`,`type_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

There is no need to store the user_login and type in this table when you can just store the user_id and the type_id. Use both of these as foreign keys to their respective tables.

Then to get the data in format that you want. MySQL does not have a PIVOT function so you will want to use a CASE statement with an aggregate function.

select ua.user_fname, 
  MIN(CASE WHEN uam.type = 'type_1' THEN ual.level END) type_1,
  MIN(CASE WHEN uam.type = 'type_2' THEN ual.level END) type_2,
  MIN(CASE WHEN uam.type = 'type_3' THEN ual.level END) type_3,
  MIN(CASE WHEN uam.type = 'type_4' THEN ual.level END) type_4,
  MIN(CASE WHEN uam.type = 'type_5' THEN ual.level END) type_5,
  MIN(CASE WHEN uam.type = 'type_6' THEN ual.level END) type_6
FROM user_accounts ua
LEFT JOIN user_access_levels ual
  ON ua.id = ual.user_id
LEFT JOIN user_access_meta uam
  ON ual.type_id = uam.id
group by ua.user_fname

See a SQL Fiddle with a Demo

This version will work if you know ahead of time the type columns that you want to get the values for. But if it is unknown, then you can use prepared statements to generate this dynamically.

Here is a version of the query using prepared statements:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MIN(case when type = ''',
      type,
      ''' then level end) AS ',
      replace(type, ' ', '')
    )
  ) INTO @sql
FROM user_access_meta;

SET @sql = CONCAT('SELECT ua.user_fname, ', @sql, ' FROM user_accounts ua
LEFT JOIN user_access_levels ual
  ON ua.id = ual.user_id
LEFT JOIN user_access_meta uam
  ON ual.type_id = uam.id
group by ua.user_fname');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See a SQL Fiddle with Demo

Answer 2

I typically use a BIGINT column and use bit masking to set the values.

For example level1 = 2, level2=4, level3=8, level4=16, etc..

Give someone level1 and level2 access:

update user set access_level = 2 & 4

does someone have level2 access?

select 1 from user where access_level | 2 AND user_id = ?

Answer 3

While I'm not familiar with the specifics of MySQL, it seems to me you are describing a pretty fundamental example of a pivot table query. What you're looking for seems reasonable to me, so I don't think based on what you've shown here I'd get too concerned about revisiting the data model. You may find putting the "level" back with the "type" table, based on the ol' saw "Normalize til hit hurts, denormalize til it works :)"

Just my $0.02. Good luck.