On Fri, 2004-04-09 at 09:48, [EMAIL PROTECTED] wrote: > Hello, > > I've recently came across a problem I couldn't seem to solve right by myself. > > I have a db with objects table, each of those objects may belong to groups > of objects. The number of groups can be about 256 and an object > belongs from one to many different groups at once. > > I cannot find a good way to store/select information about groups for > objects that belong to several groups. > > My first idea was to put a 'groups' column and keep there the > id_numbers of groups to which particular object belongs to (with > separators). So the groups column would have values like '100,203,204,208' > etc. Of course this is not the right way to do it as those values are not > atom and the lenght of the field may easily grow out of tinytext length > type I was using... > > It worked ok though for small number of groups and objects though I > know this was a perfect example of how *not* do do it in a RDB... If > I wanted an object that belonged to 2 groups (100 and 203 for example) > I would do "SELECT [...] LIKE '%100%203%'".
You definitely do not want to do it this way. You'd have to keep the
'groups' column items sorted to make sure you never have a situation
where you query for '%100%203%' when it contains (203,204,100). This
makes it complicated/non-trivial to add or remove objects from a group.
Additionally, you'd be doing a full text search for each query which is
vastly slower than querying on an indexed field.
> My next attempt was to create separate table with object_id and
> group_id column, this way an object that belongs to many groups would have
> several rows in the table. If I wanted to know what are the groups
> that my object belongs to - a piece of cake. But things get really rough if
> I want to get objects that (for example) belong to two particular (1 &
> 2) groups. I would go with something like that:
>
> SELECT DISTINCT(g1.object_id) FROM connecting_table as g1, connecting_table
> as g2 WHERE g1.group_id = 1 AND g2.group_id = 2 AND g1.object_id =
> g2.object_id;
>
> But this is getting more and more complex if I want to select objects
> that belong to 3, 4 and N groups at once :/ The first solution
> although very ugly seems to be much more clearer (and maybe even more
> efficient for groupn number of 2+?).
This is the right way and more efficient, but why are you making it so
difficult on yourself? ;)
SELECT DISTINCT object_id
FROM connecting_table
WHERE group_id = 1 AND group_id = 2;
There's no need to select the same table twice. You can make this even
simpler by using 'IN':
SELECT DISTINCT object_id
FROM connecting_table
WHERE group_id IN (1, 2)
> My working solution for now is to have a separate table with object_id
> and groups_id1 column of INT type, I have assigned the first 31 existing
> groups to corresponding bits of the INT field. That is if I want to
> select objects that belong to the groups that have ids of 1,3,5 and 26 i
> would set the bits nr 1,2,4 and 26 of a variable $MY_INT and then query:
>
> SELECT object_id,groups_id1 FROM connecting_table WHERE
> BIT_COUNT(groups_id1 & $MY_INT) = 4;
>
> this seems to give me what I want but now if I want to add next 31
> groups I need to create another groups_id2 column and things again
> get a bit complicated - I need now to check in which column the
> current group flag I need to find out may be stored and act
> accordingly (so groups with id 1-31 in column groups_id1, groups 32-63 in
> groups_id2 etc). Not a big problem and this solutions seems to be quite
> cheap (is it?) but somehow I am feeling there is a better way to
> do all this...
This has the same problems as the LIKE '%100%203%' approach, but a full
text search is replaced by math on each row. In both cases you lose the
ability to use any kind of index.
> Sorry for this long letter I hope I have managed to put the problem
> straight, I may miss something obvious here but as I
> said I have limited knowledge about sql. I would be much grateful for any
> tips.
>
>
> ------------------------
> Lecho
--
|- Garth Webb -|
|- [EMAIL PROTECTED] -|
signature.asc
Description: This is a digitally signed message part
