[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
>