"Jeffrey Santos" <[EMAIL PROTECTED]> wrote on 10/10/2005 01:07:03
PM:
> Hey all!
>
>
>
> I'm a very novice MYSQL user of the mentality of "get it working, then
get
> it working the right way," feeling the best way to learn is to just do
it.
> I got things working but now I'm looking back and trying to get better
> efficiency out of my SQL statements.
>
>
>
> Database is setup like this:
>
>
>
> Have one table using primary keys called "fcsets"
>
> Have a second table called "fcusers" with a field "vsets" which is a
comma
> separated list of primary keys referring to the first table
>
>
>
> Users can dynamically delete entries from the first table, so I want to
be
> able to remove that entry from all users who have it in their csl vsets
in
> fcusers. This is a PHP statement but I think you'll get the general
idea of
> how I'm doing it. Am looking for some suggestions on how to do this
more
> efficiently:
>
>
>
> UPDATE fcusers SET vsets=TRIM(BOTH ',' FROM CONCAT_WS(',',
> SUBSTRING_INDEX(`vsets`, ',', FIND_IN_SET('{$setkey}', `vsets`) - 1),
> SUBSTRING_INDEX(`vsets`, ',', FIND_IN_SET(REVERSE('{$setkey}'),
> REVERSE(`vsets`)) * -1 + 1))) WHERE vsets REGEXP '^.*,?{$setkey},?.*$'
>
>
>
> Where $setkey obviously refers to the key of the entry in fcsets being
> deleted.
>
>
>
> Thanks!
>
>
>
> - Jeff
>
You are having a hard time managing your set to user relationships because
of your schema (database design).
You need to normalize your data. That means you need to get rid of the
comma separated list on fcusers. You need to make one entry in some table
for each association with a set each user has.
I assume there is more information in the fcusers table other than just
what set a user belongs to and you don't want multiple copies of that
information floating about. That means you need to make a third table,
called something like `users_sets`, just to contain each pairs of PK
values of your users and the PK values of the sets they belong to. It
would look something like:
CREATE TABLE users_sets (
users_id int,
sets_id,
PRIMARY KEY (users_id, set_id)
)
Does that make sense? You create one row in users_sets for each time a
user and a set become associated. When that association ends, delete the
appropriate row.
You might be thinking that working with multiple rows of data seems like
more work, it won't be. Adding and subtracting sets to users (or users to
sets) becomes simple INSERT and DELETE statements. Everything will move
much faster.
Can you see the pattern or would you like more details?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
