Re: One to One relationships

From: --CELKO-- <71062.1056_at_compuserve.com>
Date: 26 Apr 2003 12:19:01 -0700
Message-ID: <c0d87ec0.0304261119.63fe9fd9_at_posting.google.com>


>> if the 2 items in the relationship exist separately in the real
world (can one exist without the other) then they should be separate tables. <<

Yes. Entity versus attribute, basic data modeling, and all that jazz ...

>> Obviously if you have millions of people and millions of houses
your join from people to Locations to Houses is going to take a considerable amount of
time compared to holding house as an attribute of person. <<

And what is the cost of getting the data wrong? The Locations table will be a collection of "prejoined rows" between people and houses; modern RDBMS systems do this with pointer chains, bit vectors, etc. and run really fast. If house was an attribute (or rather a non-normalized subset of attributes) within People, you would need a UNIQUE constraint on both person_id and house_id.

>> My uni lecturer once said do not do physical design (i.e. do not
denormalise for speed). <<

I agree. An amateur designs for the immediate problem, takes the money and runs; a professional designs for the future so his systems will still be running after he is gone.

>> But if I design databases like this and my competition designs
faster DBs
who gets the work? <<

If they produce the wrong answers faster than you do, what is the advantage?
I made a living fixing SQL design errors and tuning SQL code. A lot of the errors, perhaps the majority, are from bad design. The bad design came from not taking time to do it right, ignorance and trying to speed up one query or application at the expense of the whole system. Received on Sat Apr 26 2003 - 21:19:01 CEST

Original text of this message