Re: 1-1 relationships

Angus Monro wrote:
> Given that two types of entities have a one-to-one relationship, are
> there any good reasons for mapping them to two distinct tables with
> appropriate foreign-key relationships, versus mapping them into a single
> table? The theoretical side of me wants to keep them in distinct
> tables, but I can't see the point of the extra space overhead and the
> time overhead incurred by needing to do joins betweeen them when
> querying.

I don't think it is really that simple. The join-problem is largely solved by good indexing. Moreover, if you represent the the two entity-types in one table you have to access this big table, even if you only need the data from one entity type. So a simple query like SELECT A from R; might need twice as much disk access as in the split case.

Furthermore, the fact that you identified two entity types (and not one) in your conceptual data model means that the one-to-one relationship may easily one day change into a one-to-many relationship. The splitting of tables afterwards will then involve quite some rewriting of code.

-- 
  Jan Hidders