Best approach to add multiple data to a column in MySQL

Question

For example, I have Users and Projects tables.

Multiple users can be members of a project. How do I insert multiple users into the members column of the Projects table?

Do I separate by comma like: "John, Alex, Hanna"?

I'm a beginner in MySQL, sorry if this is a dumb question. Thanks!

You don't need a `Users` column in `Projects` table. Search for `relational database model`. — Xaqron, May 06 '17 at 22:21

score 3 · Accepted Answer · answered May 06 '17 at 22:31

3

You defiantly need another table. It should look like this

Name: UserProjects

Field: UserId

Field ProjectID

Those 2 fields should be primary keys (dual primary id). If you want to go down the route of 'soft deletes' then add a status column which you'll set to 0 if you delete it. Also look up insert on update for mysql.

Trust me this is the way to go. Using a delimiter field or something will only give you problems later down the line.

answered May 06 '17 at 22:31

Richard Housham

1,464
1
15
27

Here is a link to a insert on duplicate key update - that I was on about. http://stackoverflow.com/questions/4205181/insert-into-a-mysql-table-or-update-if-exists – Richard Housham May 08 '17 at 08:39

Best approach to add multiple data to a column in MySQL

1 Answers1