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]
