MySQL Array Solution?

[davidmyers]

I am currently working on a project with PHP and MySQL. Some of the requirements are that I have a user/login system, a messaging system, a calendar, and projects that the users are part of and that show up on the calendar. I am trying to find a solution to store the information for the projects and the people involved with them.

 

I have a table for users and am planning on a table for projects. I initially thought about just having a column in the projects table that would be of type array. The array would hole users associated with the project in that row. I realized however that arrays aren't a column type. I've tried thinking of many different ways to solve this, but I always run in to the same problem. I have no way of knowing how many different users will be part of any project or how many different projects any one user will be a part of.

 

I don't know if there's a way to accomplish what I want through complicated logic or if there's some way to do it using MySQL. I'm sure that there is some way to do this though.

[davidmyers]

I just had an idea but am not sure if it would work.

 

Say I have a column of type fulltext in my projects table. I could add a name into that cell with some sort of termination character at the end of it. Such as this: "John Doe;" The first question is could I write PHP code that would know to extract separate names by looking for the termination character (semi-colon in this example). The next question would be can I append information to that cell later, such as adding another name to the fulltext cell? Such as this: "John Doe; Jane Doe;"

 

This may not work or may just be a poor way of solving this problem. Let me know what you think.

[trq]

You need to look into database normalization. Storing delimetered strings or serialized arrays is going to taint your data and make it difficult to query efficiently.

[davidmyers]

I thought that doing that way would make it horribly inefficient. I'll check into DB normalization.

[davidmyers]

I did some reading on database normalization and I couldn't really find a way to accomplish what I'm wanting to do. Perhaps I didn't find some piece of information that you thought I would. How would you use normalization to fix my problem?