Parse through 'members' section to find username. 1000 times... :-(

php-phan

Ok so i have created a table called conversations. Within this is a field called members. Inside this field i will include a list of members (by username) that are a part of this conversations. Ok, now here is where the problem arises. Would it take too long to go through every database entry to try and find the few conversations that the user is apart of. For example. Say there are 10,000 conversations, well if Jon wants to view the conversations he is apart of will it take to long? I think it will, and if that is the case how can i solve this problem...any ideas?

I was thinking of created a new table called conversations_apart_of (or something like that) and have a database entry thingy for EVERY user, and then have a list of all of the conversations he is apart of (through the id). Idk what do you think?

johanafm

php-phan;10930360 wrote:
Inside this field i will include a list of members (by username)
(...)

have a list of all of the conversations he is apart of (through the id). Idk what do you think?

I don't like the use of the word "list" in this context. I'm assuming your list is a string of comma separated values or something similar. Don't do it. The second part is obiously better for this purpose, but equally nasty if you instead some day want to show people being involved in some conversations.

Create a link table, containing user_id and conversation_id.

php-phan

Ok cool. I am just curious though, why dont you like the list (yeah I was going to separate them by comma and parse through it all?). I was planning to use this in a few other features. Thanks again for that tip, cause I had did that in another database and thought it took up to much space to have it like that, but i feel better knowing i wasnt completely wrong. Thank you :-)

johanafm

Mainly because string comparison is slow, and you also end up with all sorts of other trouble.

Start by reading up on normalization

Roger_Ramjet

php-phan;10930431 wrote:
.... Thanks again for that tip, cause I had did that in another database and thought it took up to much space to have it like that, but i feel better knowing i wasnt completely wrong. Thank you :-)

No, you were completely right.

Completely wrong is NOT Normalising the database. Normalisation is not some fancy notion for db geeks: it is the only way to ensure that your database will work properly. Without it, you can easily end up with a database full of data that you can not retrieve accurately.