Re: Many to Many design help

From: larzeb (larzeb_at_community.nospam)
Date: 11/11/04 

Date: Wed, 10 Nov 2004 18:39:21 -0800

Thanks so much to you all for taking the time on this. It's given me a
lot to think about.
On Wed, 10 Nov 2004 16:48:15 -0800, larzeb <larzeb@community.nospam>
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: Many to Many design help
    ... My sense here is that living in a house and owning a house are ... HouseID int NOT NULL PRIMARY KEY, ... Owner int NOT NULL REFERENCES Persons, ... PersonID int NOT NULL PRIMARY KEY, ...
    (microsoft.public.sqlserver.programming)
  • Re: Many to Many design help
    ... Each person lives in exactly one house, ... CREATE TABLE Houses (HouseID int NOT NULL, ... has one inhabitant and a person lives in one house). ...
    (microsoft.public.sqlserver.programming)
  • Re: Many to Many design help
    ... you can see I'm struggling. ... Each person lives in exactly one house, ... Where the owner lives in the house himself, it is a one to one ... >Best, Hugo ...
    (microsoft.public.sqlserver.programming)