Re: One to One Relationship

galenboyer_at_hotpop.com said...
> On Mon, 17 Mar 2003, paul_at_paul.brewers.org.uk wrote:
> >
> > Wayne, what he's driving at is that logically it's the same
> > damn table!.
>
> Well, there is the ability for a data model to represent
> attribute inheritance and this would involve a one-to-one parent
> to child, although it would need to be one way. An equity could
> could get its id from a financial instrument table and all
> financial instruments could inherit some particular attribute or
> relationship of that financial instrument, from the financial
> instrument table.
>
> If this guy truly wants a one-to-one, yeah, that's a bad idea
> cause its the same damn table!
>
>

While I agree that a 1:1 relationship should be a rare occurance, there are some valid reasons for implementing this relationship. A few reasons I can think of off the top of my head:

1. Main customer data housed at corporate headquarters. Regional customer data housed at regional offices. Corporate can access all data in a distributed transaction.
2. The "main" record is huge (width-wise, that is) and it's determined to be more efficient to keep the columns that are accessed 90% of the time in one "narrow" table (so these rows are more likely to fit in a database block). The less frequently accessed columns are kept in another 1:1 table.
3. I want to implement sophisticated security schemes on one or more pieces of each row, so I split them off where I can more easily "hide" the data from prying eyes ... maybe on a disk that I can mount/unmount it at will.

There are probably other reasons. You have to look at your business requirements and design a database that best suites your business rules and environment. If that means a 1:1 relationship, then so be it.

-- 
/Karsten
DBA > retired > DBA