From: Carsten Gehling Date: 2009-09-15T18:59:48+09:00 Subject: Re: Pre-allocate large amount of memory? Robert Klemme wrote: > So you do actually allow for loops, i.e. company A owns 10% of company > B which owns 10% of company A. Yes that is allowed (we define some rules in the extraction code to avoid infinite recursion). > If I understand that table design properly it is awful because > semantics of columns one, three and four change based on content of > column two. The usual way would be to model this with fixed > semantics, i.e. only have one direction of ownership relation in the > table. In your case you will probably have to do a normalization step > by defining a view on this table with a UNION ALL or use a WITH clause > in the query to ensure the query can be built in a reasonable way. Actually the above structure is a view, which adds this direction. The real data-table only holds one record each relation, that is: "BasCorp", "ownedby", "BarCorp", "45%" "RteCorp", "ownedby", "QweCorp", "20%" > There are features in modern RDBMS which allow for recursive querying. > In PostgreSQL and Microsoft SQL Server you can use WITH expression: > http://www.postgresql.org/docs/8.4/static/queries-with.html > http://msdn.microsoft.com/en-us/library/ms175972%28SQL.90%29.aspx I am using Microsoft SQL Server, but I didn't know about recursive querying. > The downside is that recursive queries tend to have a performance hit Perhaps the best way will be going with my current setup (i.e. loading the entire data). But use recursive querying to reload part of the data when relations are changed. > The nested set model (Josh mentioned it as well) might help although I > haven't thought through all implications in your case: I would rather not begin to alter my data structure. > You still have the issue that you maintain redundant data and must > find a way to invalidate your cache when the base data changes. I think that I may have found a way to calculate, exactly how many records I need to reload, if the relation between two companies are added/changed/deleted. And SQL Sever's WITH option might help me there. I will have a look at that now. Thanks a bunch for your input - all of you. I will post my results, when I get there. - Carsten -- Posted via http://www.ruby-forum.com/.