whit3fir3 Posted June 17, 2011 Share Posted June 17, 2011 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 Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted June 17, 2011 Share Posted June 17, 2011 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 Quote Link to comment Share on other sites More sharing options...
mikosiko Posted June 17, 2011 Share Posted June 17, 2011 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. Quote Link to comment Share on other sites More sharing options...
whit3fir3 Posted June 17, 2011 Author Share Posted June 17, 2011 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 Quote Link to comment Share on other sites More sharing options...
mikosiko Posted June 17, 2011 Share Posted June 17, 2011 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 Quote Link to comment Share on other sites More sharing options...
ebmigue Posted June 18, 2011 Share Posted June 18, 2011 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)? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.