Storing comma seperated values in one field

Is this not a good idea?

[knighty]

I've been doing this for a while simply because I'm new to PHP and don't know how else to do it.

I read an article that said you should set up another table instead but don't see how I can acheive what I want to acheive.

Lets say I have a database of projects and each project could have 1 or many users, at the moment I would store all the names in a field called TEAM and then add users seperated by commas.

wouldnt setting up another table mean a lot more work and complicate things?

[jatar_k]

do the users have access to different projects? How many users are there?

[knighty]

yes, users have access to differnt projects and at the moment about 20+ users but could be more in future.

so..

project A ¦ bob,sue,fred
project B ¦ bob
project C ¦ sue, fred

etc.

each user has a cookie set so that only that user can view jobs they are part of.

[jatar_k]

I would think that a permissions table would work better the other way around.

I have seen these implemented in different ways.

A users table and a projects table with a 3rd table that includes a link to each and there by creates the relationship between them.

A users table with their permissions set as multiple columns (one for each project in this case) and a y/n in each col.

I have seen the setup you have with commas or just crunched together ie. ABCDE.

Though, at the end of the day, if your system works and it is maintainable for you then why change it?

[ukgimp]

Could you not have a many to many table that links the two together.

Then each combination would act as the primary key

"likingtable" (projectid and personid)
a:bob
a:sue
a:fred
b:bob
c:sue
c:fred

[lorax]

knighty,
While the normalization of your database (choosing the best way to break up the data for speed and scalability) isn't the easiest thing to do or implement, it is done for good reasons.

On first blush of what you've posted I'd suggest 3 tables, one for projects, users, and teams which is pretty close to what you've got - I think. ;)

projects would have project info and a project id.

users would contain info on the users and a user id.

teams would contain the project id along with all of the ids of the users working on that project in one field seperated by commas.

[knighty]

why change it? yeah I agree just this article got me thinking [phpbuilder.com...]

The only problem I may have with the system is that I find if the user is in the project by doing

SELECT * FROM jobs WHERE team like '%$usersname%'

which works OK but if I had john.smith and john.smithson john smith will see the other persons jobs.

[lorax]

Then reverse the teams table if you're looking at this from a user point of view. Set the primary key as the user and include the project ids seperated by commas.

[knighty]

at the moment I have 2 tables USERS and JOBS

>>teams would contain the project id along with all of the ids of the users working on that project in one field seperated by commas.

this is pretty much what I have execept users are seperated like john.smith,fred.bloggs IN the JOBS table.

Like I said before I'm still experimenting so any guidelines would be appreciated.

I'm just not sure why creating another table for USERS is better than my current set up or how I would identify each user wiht a project (I'm gonna look into ukgimp's suggestion)

[knighty]

lorax,

i already have use the firstname.surname as the primary key in the USERS table so I would just add each project ID into a new field?

but then would'nt that mean using 2 select statements? 1 to get the users projects and 1 to get the job depending on the info pulled before? that seems like more work.

[lorax]

>> i already have use the firstname.surname as the primary key in the USERS table so I would just add each project ID into a new field?

You could do this.

>> but then would'nt that mean using 2 select statements? 1 to get the users projects and 1 to get the job depending on the info pulled before? that seems like more work.

Naw. Just use a left join [mysql.com].

[lorax]

Whoops - I should qualify that last statement. If you use the 3 table scenario and you query for a user and want all the projects that user is assigned to then you'd use something like:

SELECT projects.*
From projects, teams
Where projects.p_id LIKE %teams.p_ids%

Caveat: I haven't test this exact syntax but in theory it should work.

[knighty]

Is it just my or is Mysql.com explantions not written very well.

I find it really confusing..is there a n00b version somewhere?

Thanks lorax will look into your reccomendations tommorrow as I'm off home now :)

I have some stuff to think about now as I also have other tables and pages which will be affected.

[lorax]

Well, MySQL is written very well but it isn't necessarily easy. But you'll find this with any robust db. It is worth learning and I would encourage you to visit the MySQL website [mysql.com]. You'll find a lot of excellent information there. Good luck with it and you know what to do if you get stuck. Besides, Andreas may come along and correct me any moment now... ;)