Re: Many to Many design help

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Steve Kass (skass_at_drew.edu)
Date: 11/11/04 

Date: Wed, 10 Nov 2004 20:49:26 -0500

Larzeb,

  My sense here is that living in a house and owning a house are
separate facts, and I would consider a model like this:

CREATE TABLE Persons (
  PersonID int NOT NULL,
  -- other attributes of a person
)

CREATE TABLE Houses (
  HouseID int NOT NULL PRIMARY KEY,
  Owner int NOT NULL REFERENCES Persons(PersonID),
  Resident int NULL REFERENCES Persons(PersonID)
  -- other attributes of a house
)

Using Resident IS NULL to indicate that the house is unoccupied is not
ideal, but there are some
benefits here - owner-occupied houses are those for which Owner=Resident.

Another option is

CREATE TABLE Persons (
  PersonID int NOT NULL PRIMARY KEY,
  -- other attributes of a person
)

CREATE TABLE Houses (
  HouseID int NOT NULL PRIMARY KEY,
  Owner int NOT NULL REFERENCES Persons(PersonID),
  -- other attributes of a house
)

CREATE TABLE Persons_in_Houses (
  PersonID int NOT NULL UNIQUE REFERENCES Persons(PersonID),
  HouseID int NOT NULL UNIQUE REFERENCES Houses(HouseID),
  PRIMARY KEY (PersonID, HouseID)

An advantage here is that you could more easily use different base
tables for
Person-Residents and Person-Owners. I can imagine that different
things are important about an owner and a resident, and in the real world,
houses are often owned by entities that are not persons. Houses are also
occupied by multiple people sometimes, but distinguishing one
Resident-of-record
might still make sense.

SK

larzeb wrote:

>Hugo,
>
>Thanks for your help; you can see I'm struggling.
>
>Your assumptions were correct except for #3, Each house is inhabited
>by only one person.
>
>
>
>>ASSUMING THAT
>>1. Each person lives in exactly one house,
>>2. Each person owns 0, 1, or more houses,
>>3. Each house is inhabited by 1 or more persons, and
>>4. Each house is owned by exactly one person,
>>
>>
>>
>
>I can't understand how your suggestion might work in the situation
>where I am living in house A and you own house A. In that case I will
>need 2 Persons associated with the same house (A). In that case it is
>a one to many (House to Person) relationship.
>
>Where the owner lives in the house himself, it is a one to one
>relationship.
>
>Thanks again, Larzeb
>
>
>
>>CREATE TABLE Persons (PersonID int NOT NULL,
>> LivesInHouse int NOT NULL,
>> PRIMARY KEY (PersonID),
>> FOREIGN KEY (LivesInHouse) REFERENCES Houses
>> )
>>CREATE TABLE Houses (HouseID int NOT NULL,
>> OwnedBy int NOT NULL,
>> PRIMARY KEY (HouseID),
>> FOREIGN KEY (OwnedBy) REFERENCES Persons
>> )
>>
>>Of course, if my assumptions are wrong, the model has to change.
>>
>>Best, Hugo
>>
>>
>
>
>

Relevant Pages

  • Re: can child reference fathers member?
    ... > int IsHeightOk; ... There is no relationship between the chair class and the house class, ... In order for the chair class to access a member of the house class, ... a house object, or a pointer or reference to a house object, that is in ...
    (microsoft.public.vc.language)
  • Re: Many to Many design help
    ... person_id int not null, ... Person 2 lives in House 2 and owns it ... persontable.person_id as resident ... persontable.person_id as owner ...
    (microsoft.public.sqlserver.programming)
  • Re: scott henderson - wow
    ... >> just found this on google, ... > He's good int he? ... :-) I've got Tore Down House and Well to the Bone in the ...
    (uk.music.guitar)
  • Re: Trying to understand pointers for function paramaters
    ... > In a function without pointer arguments, ... > int main ... The house is your int object. ... Well you simply say, set fire to this ...
    (comp.lang.c)
  • Re: Questions
    ... oldest known remain of a house is? ... your C-essay to find references to changing water levels. ... Somehow the actual title of the report was omitted, ... Recent sedimentary deposits and sediment chemistry"). ...
    (sci.archaeology)