Table Design Question....

[whit3fir3]

I have a database design that I need to see if I can help point me in the right direction on.  In my database I have 2 tables that are related.  TABLE A is a list of options that can apply to records in TABLE B.  It is a Many to Many type relationship between the two tables.  My initial idea was in TABLE A give ID's that were powers of 2.  The first record would have the ID of 1, the second record would have an ID of 2, the third record would have an ID of 4 and the 4th record would have an ID of 8.  Then in TABLE B I could just store an INT of the sum of the values that apply to the record.

 

My problem is that there are 116 records in TABLE A and 2^116 is a very large number.  Over time this number might increase from 116 as well.  An unsigned BIG INT in MySQL will not hold an int that large and I really do not want to deal with saving it in the DB as a string and then converting it back to a number before doing the math on it.  My question is basically is there a better maybe even simpler way in MySQL to create a many to many relationship between tables like this?

 

Thanks,

 

whit3fir3

[Muddy_Funster]

could you please post up your current table structures...I'm having a hard time getting my head round what you are doing here....it sounds insane

[mikosiko]

Huh?.... don't follow.... (and I'm not talking about your number problem)... why you don't explain what exactly you are trying to accomplish with a design like the one you described... your tables schema and data examples will help to offer you suggestions.

[whit3fir3]

Sorry if this wasn't explained very well.  I try and keep things short and to the point and don't always describe things clearly.  The basic design of the tables in question is below:

 

TABLE A

ID  -  VALUE_NAME

1  -  Cat

2  -  Dog

4  -  Bird

8  -  Fish

 

Table B is just storing information about people.  In this example lets say in TABLE B I have a column named PETS_OWNED.  For the first person that column had a value of 5.  I would know that the person in question owned a Bird and a Cat as there are no other possible combination of numbers that will give you a value of 5.  Now for what I am trying to do there are currently 116 different PETS (for this example) that the person could own and any one person COULD own one of each 116 different pets.  The problem I am running into is 2^116 is larger than a UNSIGNED BIG INT and I was just wondering if there is a better maybe even simpler way to accomplish this same thing?

 

Thanks,

 

whit3fir3

[mikosiko]

that is a design calling for problems... one example:

what about if one person own:

1 Dog, 1 Bird and 2 Cats ... that will add-up 8 right?.....  so now... how you can tell what exactly he own?... all those pets or just a Fish?

 

you will be better if you create a new table that represent the relationship between the pets and the owners... like per example:

Pets_by_owner

Pet_id

Owner_id

 

with that you can have with simple SELECT the exact data that you need

[ebmigue]

Inherently there is nothing wrong with your database design. IMO, its unconventional.

 

But it could run into problems, which otherwise will not happen if you stick to classical database design theory.

 

You could use a DECIMAL or FLOAT data types if you insist on your method.

 

Just curious, what will be your query when you retrieve a given person's pet(s)?