Re: Three table database - period (?)

Jens wrote:
> Jan Hidders wrote:
>
> > Er, let me get this straight. In order to avoid expensive joins you
> > are going to do them yourselves? Most databases are especially
> > optimized to do exactly that sort of thing. Are you smarter then
> > the people who built those databases? :-) Especially if the joins
> > are so big that they won't fit into main memory programming it
> > yourself is going to lead to a lot more disk access.
>
> No, you are right. I should not assume that I can do it better than the
> database (not before I have tested it :-).

Ah, that is the attitude I like. Nice logical sounding stories are all well and good but they don't come close to actually rolling up your sleeves and finding out if it is *really* true. :-)

> I was thinking that I might
> gain something if for some method in an object I didn't need to make a
> full join, that is from the top of the entity down to the attribute(s) in
> question (a multiway join). I just go straight to the attributes as I
> already know enough information (stored in the object) to get there. Am I
> missing something?

You have to keep in mind that the most important cost factor is the number of disk reads that need to be performed. If you are just retrieving one normal record then this will probably just be one read (excluding the reads for looking it up in the index). But in your model you would probably need 1+n reads where n is the number of attributes you want (and again, I am not counting the reads needed for lookup). So, although in the first case you have retrieved information that you don't really need, the number of disk reads is still lower.

Btw, did you consider using an object-oriented database?

-- 
  Jan Hidders