[AccessD] One-to-One relationships

Actually, she was a fan dancer, which is different from a stripper,
since they start out without much on and wave the fans around to expose
bits at a time.  She was quite famous in her time.

Charlotte Foust 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Friday, July 20, 2007 6: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.

On 7/20/07, Susan Harkins <ssharkins at setel.com> wrote:
>
> Yes, if it has a purpose. A one-to-one relationship almost always 
> flows from need rather from the data itself. If you need to force a 
> one-to-one, I'd say do it. However, if there's no business rule 
> saying, "there can be only one..." it might be unnecessary, even if 
> the data is presenting that picture right now. Listen to the data.
>
> Susan H.
>
> Is there any purpose/advantage in creating a one-to-one relationship 
> in a database (e.g., CustomerId and CustomerName in one table and all 
> the other customer data (e.g., sex, address,  phone, etc) in another
table?
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com