Assigning multiple values to a single column in MySQL

mysql

Solution

I wouldn't store multiple values in an accessible_projects column. I'd make a link table, called `user_accessible_projects`, which would have the user_id and project_id. So if user X had 5 accessible projects, this table would have 5 rows for user X.

Here's a simplified table structure:

===========
user table
===========
user_id

=============
project table
=============
project_id

==============================
user_accessible_projects table
==============================
user_id
project_id

I would also recommend having a unique index (or primary key) on user_id and project_id in user_accessible_projects to insure data integrity, e.g. you don't want there to ever be 2 rows with exact same user_id and project_id in this table. And you would also want foreign keys from the user_accessible_projects table back to the user and project table. The foreign keys will ensure that a record can't be deleted from the user or project tables if there are still associated rows in user_accessible_projects.

If you're not familiar with referential integrity in databases, you can read more about it here. It pays huge dividends on keeping your data clean and tidy so that no bad data ever gets into the database. After all, the best way to prevent bad data is to never let it happen in the first place.

Problem

I'm working on a project right now where a user has an active project that they can clock in and out of, as well as a list of projects they can access and select to be their active project. An admin controls what projects the user can access. What would be a good way to store the active and accessible projects for the user, as well as a list of all the projects that an admin can allow access to? Right now I have a table containing all the projects, and a project column in the users table containing their current active project. Would it be possible to create a column called "accessible_projects" and assign multiple values therein?

Original source