Here''s the scenario. I have a main tree-like table (actually it''s more of a hierarchy), where the entries (or at least some of them) can be categorised as one or more of a fixed number of types (prob about 6 poss types). So an entry could be a Type A and a Type D; a Type C, D, E; just a Type F; etc. Having a join table would seem like it might get rather expensive since I''m going to want to know the types each time I call an entry. Seemed an obvious way would be to use an integer and bitwise ops, so that each bit in the integer represrented a type which could be on or off. Does anyone have any comments re the wisdom or otherwise of doing this? Would the benefits be outweighed by the calculation overhead? Any better way that I haven''t thought of? Cheers in advance Chris T (p.s. I''m using MySQL at the moment) -- Posted via http://www.ruby-forum.com/.
I personally think the join table is a cleaner solution simpy because Rails provides such good support for it. However, I agree it can be overkill. At DevLists, we needed to keep track of the read/unread messages for each mailing list a member subscribed to. I originally used a join table, but the thought of 10K members needing to keep track of 100K messages made my colleague think I was nuts (1 billion records is nothing to sneeze at, even for MySQL). The approach we took was to add a TEXT field to one of the tables and storing some YAML into it. This way we could easily optimize the database. I hope that helps. On Tuesday, March 14, 2006, at 7:22 PM, Chris T wrote:>Here''s the scenario. I have a main tree-like table (actually it''s more >of a hierarchy), where the entries (or at least some of them) can be >categorised as one or more of a fixed number of types (prob about 6 poss >types). So an entry could be a Type A and a Type D; a Type C, D, E; just >a Type F; etc. > >Having a join table would seem like it might get rather expensive since >I''m going to want to know the types each time I call an entry. Seemed an >obvious way would be to use an integer and bitwise ops, so that each bit >in the integer represrented a type which could be on or off. > >Does anyone have any comments re the wisdom or otherwise of doing this? >Would the benefits be outweighed by the calculation overhead? Any better >way that I haven''t thought of? > >Cheers in advance >Chris T > >(p.s. I''m using MySQL at the moment) > >-- >Posted via http://www.ruby-forum.com/. >_______________________________________________ >Rails mailing list >Rails@lists.rubyonrails.org >http://lists.rubyonrails.org/mailman/listinfo/railsCheers! --Dave Teare http://devlists.com - Email list management http://palmsphere.com - Apps for your hand-held -- Posted with http://DevLists.com. Sign up and save your time!
I hadn''t thought of YAML (haven''t used it before), though I had wondered about using a string composed of the types ''AD'', ''CDE'', ''F'' etc, which would be halfway between the two and might work better that bitwise ops (I need to be able to do a select of those entries of Type B, for example). Would YAML be easier/quicker to parse? How about select statements? From a quick look at the Pickaxe (I''m a ruby nuby) YAML seems pretty straightforward to use in Ruby -- always a bonus ;-) Dave Teare wrote:> I personally think the join table is a cleaner solution simpy because > Rails provides such good support for it. However, I agree it can be > overkill. At DevLists, we needed to keep track of the read/unread > messages for each mailing list a member subscribed to. I originally > used a join table, but the thought of 10K members needing to keep track > of 100K messages made my colleague think I was nuts (1 billion records > is nothing to sneeze at, even for MySQL). > > The approach we took was to add a TEXT field to one of the tables and > storing some YAML into it. This way we could easily optimize the database. > > I hope that helps. > > On Tuesday, March 14, 2006, at 7:22 PM, Chris T wrote: > >> Here''s the scenario. I have a main tree-like table (actually it''s more >> of a hierarchy), where the entries (or at least some of them) can be >> categorised as one or more of a fixed number of types (prob about 6 poss >> types). So an entry could be a Type A and a Type D; a Type C, D, E; just >> a Type F; etc. >> >> Having a join table would seem like it might get rather expensive since >> I''m going to want to know the types each time I call an entry. Seemed an >> obvious way would be to use an integer and bitwise ops, so that each bit >> in the integer represrented a type which could be on or off. >> >> Does anyone have any comments re the wisdom or otherwise of doing this? >> Would the benefits be outweighed by the calculation overhead? Any better >> way that I haven''t thought of? >> >> Cheers in advance >> Chris T >> >> (p.s. I''m using MySQL at the moment) >> >> -- >> Posted via http://www.ruby-forum.com/. >> _______________________________________________ >> Rails mailing list >> Rails@lists.rubyonrails.org >> http://lists.rubyonrails.org/mailman/listinfo/rails >> > > > Cheers! > --Dave Teare > http://devlists.com - Email list management > http://palmsphere.com - Apps for your hand-held > > >