Re: Data modelling questions.

A one-to-one relationship may also be used for performance purposes if one portion of the data is heavily updated with joins. Ordinarily this would cause the expense of deferred updates for the whole row. By vertically segmenting off the volatile and hopefully unindexed portions, you can avoid most of the penalty.

Jay Buckler
Robert Winkler wrote in message <344784C8.7DFB_at_arl.mil>...
> 3) Both of the entity sets participate totally in the relationship:
>
> This was the most puzzling case to us. Since every talent has both an
>associated name and photo, there does not seem to be any logical reason
>(from a conceptual data modeling perspective) why these two should not
>be combined into a single entity set. There may however be performance
>reasons for not doing so (based on an understanding of the typical
>physical implementation of a relation in a RDBMS - C.J.Date would
>admonish me here for conflating the physical and logical models, point
>taken). Typically an RDBMS will store all of the attributes of a
>relation contiguously on a disk page. If one of the attributes is many
>times larger than the others (as is the case for talent photos vs.
>talent names - depending, of course, on how the DBMS implementation
>stores BLOBS or other large objects) then access to the smaller
>attributes will be inhibited by the fact that there will be many less of
>them per page than would be possible if the large attribute was stored
>elsewhere on different pages. So if there are a lot of queries which
>reference only the smaller attributes, and we are not exactly sure how
>or where our DBMS will store the large attributes, then to play it safe
>we may wish to retain these as separate entity sets to minimize the
>number of disk pages read when accessing the smaller attributes (talent
>names). (Conversely, if the majority of our queries involve both name
>AND photo then we would probably merge them into a single entity set.)
>
> Note that in this case we would still like to maintain referential
>integrity between the 2 entity sets. Since the referential integrity
>constraints are circular here, we would have to use SQL-92 assertions
>instead of table constraints.
>
> Note finally that similar arguments could be made for distributed and
>multi databases.
>
> Hope this helps.
>
>--
>Robert Winkler (Will Code for Food)
>U.S. Army Research Laboratory
>Beckman Institute, Room 4357
>405 North Matthews Ave, Urbana, IL 61801
Received on Fri Oct 17 1997 - 00:00:00 CDT