Best way to arrange a table with multiple values for 1 row...?

1

Hi :slight_smile:

I’m not sure what is the correct terminology to use…I’d like to know what is the best way to arrange a table like this:


+----+----------------+-------------+--------------------------------------+
| id | user_privilege |    name     |             description              |
+----+----------------+-------------+--------------------------------------+
|  1 |             -3 | Blacklisted | Read only user                       |
|  2 |             -2 | Flagged     | Under review                         |
|    |                |             | Privileges unaffected                |
|  3 |             -1 | Newbie      | General user                         |
|    |                |             | Monitored transactions               |
|    |                |             | Restricted activity                  |
|  4 |              0 | Verified    | General user                         |
|    |                |             | Unrestricted transactions            |
|  5 |              1 | Assistant   | View user privileges                 |
|    |                |             | View user activity                   |
|    |                |             | Customer support                     |
|  6 |              2 | Moderator   | View user privileges                 |
|    |                |             | View user activity                   |
|    |                |             | Customer support                     |
|    |                |             | Change user privileges below current |
|    |                |             | Reverse Payments                     |
|    |                |             | View user identification             |
|  7 |              3 | Admin       | View user privileges                 |
|    |                |             | View user activity                   |
|    |                |             | Customer support                     |
|    |                |             | Change all user privileges           |
|    |                |             | Reverse Payments                     |
|    |                |             | View user identification             |
|    |                |             | Admin discretion                     |
+----+----------------+-------------+--------------------------------------+

So far my table structure looks like this:

drop table if exists user_privileges;
create table user_privileges (
id int(7) auto_increment primary key not null,
user_privileges tinyint(2) default -1 not null,
name varchar(256) not null,
description varchar(256) not null
);
insert into user_privileges values
	(null, -3, 'Blacklisted', 'Read Only User'),
	(null, -2, 'Flagged', 'Under Review'),
	(null, -2, 'Flagged', 'Privileges Unaffected'),
	(null, -1, 'Newbie', 'General User'),
	(null, -1, 'Newbie', 'Monitored Transactions'),
	(null, -1, 'Newbie', 'Restricted Activity'),
	(null, 0, 'Verified', 'General User'),
	(null, 0, 'Verified', 'Unrestricted Transactions'),
	(null, 1, 'Assistant', 'View User Privileges'),
	(null, 1, 'Assistant', 'View User Activity'),
	(null, 1, 'Assistant', 'Customer Support'),
	(null, 2, 'Moderator', 'View user privileges'),
	(null, 2, 'Moderator', 'View user activity'),
	(null, 2, 'Moderator', 'Customer support'),
	(null, 2, 'Moderator', 'Change User Privileges Below Current'),
	(null, 2, 'Moderator', 'Reverse Payments'),
	(null, 2, 'Moderator', 'View User Identification'),
	(null, 3, 'Admin', 'View user privileges'),
	(null, 3, 'Admin', 'View user activity'),
	(null, 3, 'Admin', 'Customer support'),
	(null, 3, 'Admin', 'Change All User Privileges'),
	(null, 3, 'Admin', 'Reverse Payments'),
	(null, 3, 'Admin', 'View User Identification'),
	(null, 3, 'Admin', 'Admin discretion')
	;

I’ve no idea whether I should keep the structure this way or if there’s a term or special method for this type of table structure.

Can anyone point me in the right direction? (Using MySQL btw)

2

Yes,
Im not a php or mysql expert but ive been studying over the last two months and your question actually just helped shed some light on something im trying to figure out, creating an application that allows users to create a user profile and upload multiple images in a form under their unique profile, and then we can see all of the images under each user name. however each user profile also has its own table of information. i.e. name, description etc…

This is similar to what your trying to do, however if you replace all the user privileges with images (as in my case).

The main idea is this:
whenever we see entering repetitive information into a database table we assume its not the correct way to do it. in your case all of the permissions you have under your description column have caused you to enter alot of repetitive information. it is more correct to create multiple tables and make them relational

you may think to store the values you have under the description column into an array and this would save you alot of repetition however i dont think the database supports an “array” field. so this approach is incorrect.

read this post on stackedoverflow it describes in more detail what you have to do:
how to store arrays in mysql

here also is a youtube video explaining the concept of primary and foreign key
foreign key vs primary key

after you read the article and responses on stacked, and watch the video and think about you will see the better way is to build two tables, and you will construct any new temporary tables combining the two should be done through the query itself.

edit:
correction : certain mysql may support a json type , so you could do it that way… if you just dont want to make more than one table.

another point, when you decide which route to go imagine if in your case your description category grows to hundreds of descriptions can you imagine having to rewrite all that information for each user and how long that would take you.

2 Likes
3

Thank you for that :slight_smile: The links were helpful and taught me pretty much!