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.
>
> thus
>
> Talent (talent_id, talent_name)
> TalentPhoto (talent_id, talent_photo)
>
> so they used the same unique identifer for Talent and TalentPhoto
> to create the association.
>
> .....
>
> Another strange approach that somebody also suggested to me was.
>
> Talent (talent_id, talent_name)
> TalentPhoto (talent_photo_id, talent_photo, talent_id)
>
> ie. a back pointer from talentphoto back to the associated talent.
>
> I assume this is appropriate when not other entity would need to associate with
> a talent photo. Ditto the other approach above. and if you need mulitiple
> entities associated to a talentPhoto then the prefered approach is a foriegn
> key in each entity pointing to the talentPhoto.
>
> ------
>
> I would like to read about when each method is appropriate. Are there any
> references that discuss these issues?

Hi,
  Just a question if it is really a one to one relation , Whats stopping you to merge them into one table ?

   I also have the same problem with one table when I was improving the design of an insurance company . The original designor chosed to divide the table into two instead of just having one because of his reason that most of the detail of another table were often not needed and he kinda like to have smaller table than one big one. . But I made sure that the one to one dependecy exist between the two table by enforcing the right foreign key and primary keys .. That way table B which is dependend on table A will never only have one detail for each entry in A.

 So far we never had any problem when it comes to the consistence of the data .

    The second scheme that you mentioned (i.e) having Talent_photo_id which I assume to be the primary key may also work , provided that you are going to enfoce a unique constraint on Talent_id on the Talent_photo table , otherwise youll end up with a one to many relation .

     I hope this helps in making yor decission .   

Au revoir . Received on Thu Oct 23 1997 - 00:00:00 CDT