I want to know how to solve the group/members-problem in the best possible way.
Let's say I have a db with two tables, one for individuals and one where the are groups defined.
In my example there are about 800 individuals in many different groups (up to 150) and the individuals can be members of many groups at the same time.
I know at least three ways to solve this with MySQL but I don't know which is the best way when it comes to speed and structure.
1. I can create a table which holds the individual's index and also the group's index.
In this table an individual will have as many rows as he/she is member of groups.
Let's say that an individual is a member of ten groups in average. It will give this table approximately 4000 rows. To me this seems to be the "cleanest" way but when I think "4000 rows" I get chills...
2. I can use the table with the individuals and store a string for each individual which hold ones ans zeros only. The string must have as many characters as there are groups in the system.
The string "0010110101001" means that the individual is a member of groups 2, 4, 5, 7, 9 and 12.
The drawback might be that MySQL will lag storing all those strings.
Also it might lag when checking for membership (='1') in the string.
3. I can use the idea last mentioned but translate the ones and zeros into a decimal number (with bindec) whihc is stored instead of the string.
Each time I want to list the groups for each meber I have to translate the integer back to a string and check if there was a "one" on the groups position in the string.
The problem is that with a large number of groups it will not work because 2 powered to 150 is too large for any MySQL integer type.
Which of the above solutions is the best and why?
Is there another (better) way to solve this?
Let's say I have a db with two tables, one for individuals and one where the are groups defined.
In my example there are about 800 individuals in many different groups (up to 150) and the individuals can be members of many groups at the same time.
I know at least three ways to solve this with MySQL but I don't know which is the best way when it comes to speed and structure.
1. I can create a table which holds the individual's index and also the group's index.
In this table an individual will have as many rows as he/she is member of groups.
Let's say that an individual is a member of ten groups in average. It will give this table approximately 4000 rows. To me this seems to be the "cleanest" way but when I think "4000 rows" I get chills...
2. I can use the table with the individuals and store a string for each individual which hold ones ans zeros only. The string must have as many characters as there are groups in the system.
The string "0010110101001" means that the individual is a member of groups 2, 4, 5, 7, 9 and 12.
The drawback might be that MySQL will lag storing all those strings.
Also it might lag when checking for membership (='1') in the string.
3. I can use the idea last mentioned but translate the ones and zeros into a decimal number (with bindec) whihc is stored instead of the string.
Each time I want to list the groups for each meber I have to translate the integer back to a string and check if there was a "one" on the groups position in the string.
The problem is that with a large number of groups it will not work because 2 powered to 150 is too large for any MySQL integer type.
Which of the above solutions is the best and why?
Is there another (better) way to solve this?