[AccessD] One-to-One relationships

Arthur,

You've been on about strippers as long as I've known you.

;-)

John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Friday, July 20, 2007 9:24 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] One-to-One relationships

I would respectfully suggest that you're overlooking something in your
analysis, Susan, but to observe it you need millions of rows in the given
table. But suppose that you have a table called Customers, which as
previously suggested in this thread might include both corporations and
persons. The first rule of database development is performance, above all
other considerations. Therefore one ought to identify the columns of
immediate interest (CustomerID, CustomerName, etc.) and store those in a
single table, pushing all the other attributes to one or more related tables
with a 1:1 relationship. This way, I can search a small table with multiple
indexes very quickly, and not bother with fetching the rest of the data
until you explicitly request it, at which point it would be a
lightning-quick sproc that receives a CustomerID and sends back the rest of
the data. If you really want to push the performance button, then you won't
return a rowset either. Instead you'll declare as many parameters as you
have columns of interest, and declare them all Output parameters. When you
want exactly one record, that's the quickest method.

I hope I didn't obscure the point here. The point is what I call the Sally
Rand principle. (You might have to be older than even I to understand the
reference -- she was a famous stripper, back when stripping meant that you
still retained most of your clothes.) Her point was, show them as little as
possible to still maintain their interest. That's my motto in terms of
database design. Never open an entire table. Show them only enough to pique
their curiosity, as it were.