Re: Data modelling questions.

Robert Nicholson wrote:
>
> Recently I came accross an example database that modelled one-one relationships
> in a way I have not seen before.
>
> Say I have two entities.
>
> Talent (talent_id, talent_name)
> and TalentPhoto (talent_id, talent_photo)
>
> For arguments sake let's make this a one-to-one. In the example they had
>
> schema defined such that there was no foriegn key in Talent pointing to it's
> associated photo.

Hi Robert,

We have pondered 1:1 relationships some in the past. The question we considered was when it is desirable/necessary to model this relationship as two separate entity sets as opposed to a single entity set.

1. One entity set participates only partially in the relationship:

        If one of the entity sets only has partial participation in the relationship (e.g., if all talents have a name but not all talents have an associated photo then Talent participates only partially in the relationship with TalentPhoto) then we might want to model these as separate entity sets to avoid the introduction of null values for those talents w/o associated photos. In this case, however, TalentPhoto should have a FK referencing Talent.talent_id (since TalentPhoto has total participation; i.e., there is always a Talent entity for every TalentPhoto entity). If there is an FK from TalentPhoto to Talent but not one from Talent to TalentPhoto, I would infer that this is the case in your example.

   2) Both of the entity sets have partial participation:

        If both of the entity sets participate only partially in the relationship then there are some talents which have associated names, some talents which have associated photos, and some talents which may have both. Again to avoid the introduction of null values we may wish to model these as separate entity sets (although in this case there would be no FK constraints). If there are no FKs from TalentPhoto to Talent or vice versa, I would assume that this is the case in your example.

   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