[RESOLVED] manipulating arrays stored in mysql

etoast

Hello
I have a database with say 1000 records. I allow registered users to select as many of those records as they like and save them against their profile. So for example, user "Bill" may have records, "13,423,687,693" saved in his profile.

I can do that, but what I am struggling to conceive is how to allow user "Bill" to update his preferences. For example, if he wants to drop 432 from that array. He has no idea what 423 means as he is picking from something more real. 423 is just the ID of the record that he has selected.

Perhaps my question is too vague, but I'm struggling to get going in one direction and wondered whether this rings any bells for anyone.

Any suggestions much appreciated.
Regards

Piranha

The problem is probably that you store arrays in a database instead of normalizing the tables. Read up on database normalization to understand why it is wrong to store an array directly in the database.

etoast

Interesting thanks .
I understand the concept but does it really benefit me? Using those normalisation examples I should have a table like:

ID / URL ID / USER ID
1 1 1
2 1 2
3 3 1
etc

If I have 1000 possible URL IDs and 1000 USER IDs and each user is picking between 1 and 50 URL IDs that could be 50000 records. Won't that be really slow?

I suppose it may be easier to add and delete records one at a time, rather than trying to update an array. hmm, thank you, you have given me something to think about.

Cheers.

Piranha

Databases is built to handle millions of rows, so no it won't be slow. As an example this forum store all posts in the same table, and the count is over 10 million. Ok, some are removed but there are probably still a couple of millions of rows in the database.

And yes, the table might look like that. Another way to build the table since it is the "connection" table is the following:

URL ID / USER ID
1 1
1 2
3 1

And then you use both together as the primary key. Some prefer one way, others the other. There is no right or wrong with this.