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%'.
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+?).
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...
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
