What's the best entity relationship here?

[aynrandgirl]

We have:

Property

Companies

People

Each Property is owned by 1 or more Companies or People

Each Company is owned by 1 or more Companies or People

People are owned by nobody

[Fenton]

You need at least 2 "join" files.

CompaniesProperties*

CompaniesPeople

These will be simple files, with mostly the IDs of their 2 entities, a creation date, and anything else unique to this combo.

*If, however, 1 particular person can be in more than 1 company, then this file should be PeopleProperties.

If Company to People is many-to-many you could sort out the companies from the person, but not the other way around.

[kennedy]

Need more info...

Are the fields of Company a strict subset of the fields of Property?

Are the fields of Property a strict subset of the fields of Company?

Will there be other entities for which Property will be a strict subset of?

If neither are strict subsets, what are the fields that will be common between Property and Company?

[aynrandgirl]

Other than the ownership relation, the fields of Company and Property should be disjoint sets. The same is true of People & Property and People & Companies. Note that while People may also be Employees of a Company, that's a different relationship than the one shown here (Ownership of a Company).

The thing that bugs me is that it looks like 4 joins (therefore, 4 portals), and I wanted to simplify if possible.

There are:

CompaniesOwnProperties

CompaniesOwnCompanies

PeopleOwnProperties

PeopleOwnCompanies

I was also wondering if an "is owned by" relation would be better than an "XOwnsY" relation.

[kennedy]

If when looking at a Person or Company you want one portal with all the stuff they own (rather than one for the Companies they own and one for the Property),

AND when looking at a Company or Property you want one portal with all their owners (whether Company or Person),

THEN one option would be to create a single additional file "Owners" that has fields PersonOwner, CompanyOwner, CompanyProperty, and OtherProperty. Make sure that only one of the two Owner fields is set at a time and that only one of the two Property fields is set at a time. Then, for example, Company will have two Relationships, one called OWNERS which matches its ID to the CompanyProperty field in Owners, and another called PROPERTY that matches its ID to the CompanyOwner field in Owners. Similar to the former in the Property file and similar to the latter in the People file.

[aynrandgirl]

As I understand your solution, I have a relation between Companies::CompanyID and Owners::CompanyOwner, and a relation between Owners::CompanyProperty and Property::PropertyID. That gives me what Properties are owned. A different Owner record would have a relation between Owners::OtherProperty and Companies::CompanyID. But since relations are fixed, how do you dynamically choose which relation (Owner->Property,Owner->Company) you are joining with but a single join file?

[kennedy]

Yes, the relationships are fixed... and they all are there... you "dynamically choose" by which one you use for specific purposes. For example, when you are setting up the portal in your Company file to display all that it owns, you use the PROPERTY relationship, accessing fields PROPERTY::CompanyProperty and PROPERTY::OtherProperty. The Owner records that relate to a PeopleOwner will simply not show up because their PeopleOwner fields will be blank.

You say "A different Owner record would have a relation between Owners::OtherProperty and Companies::CompanyID." First, the relationships aren't record-by-record. Its just that the relationships that don't "apply" will just not match because they have blank values in those fields. Second, there will be no relationship between OtherProperty and CompanyID.

Does that make sense?

[enzo01]

aynrandgirl said:

I was also wondering if an "is owned by" relation would be better than an "XOwnsY" relation.

The good thing when involving a Join file is that both relationships "Is Owned by" and "XOwnsY" could be established...

What seems to be confusing here is the People/Company affiliation. If a Contact may be an owner as well as a single employee from the Company File (through PeopleToCompany Joinfile), you should either :

- create a new file called 'Owners'

- drop each Contact with a direct relationship to the Property into the 'Company' file.

I'd personnaly go for the second solution.

Think of new name for your 'Company' file if you wish (Actors ?), but an owner may have an Unique_Id in the 'Company' and another in the 'Contact File'.

This way, your Join PeopleToProperty would always involve the Company file and the Property File.

[aynrandgirl]

Ok. Let's see if I got this right. Let's say I have a Company, with 1 Person owner and 1 Company owner. There would be a relationship between Company::CompanyID and Owners::CompanyProperty. In Owners, there would be two records: one in which PeopleOwner is set, and CompanyOwner is not, and one in which CompanyOwner is set, and PeopleOwner is not. Similarly, if I wanted to know what properties the Company owned, I would match Company::CompanyID and Owners::CompanyOwner. Each record would have either CompanyProperty set, pointing to an owned Company, or OtherProperty would be set, pointing to a different property database. Is this correct?

The second part of my question is, in the Name field for the portal showing the owners, how does it know that if PeopleOwner is is set, it must display People::FullName, and if CompanyOwner is set, it must use Company::CompanyName?

[enzo01]

Not sure to whom your answer was....

If it was to my statement, there would be only 2 main key fields in the Owner.db which are the Company_ID and the Property_ID.

Moreover, the Owner.db could be considered as a new Joinfile to establish these Many owners to one Property you were talking about.

[kennedy]

aynrandgirl said:

Ok. Let's see if I got this right. Let's say I have a Company, with 1 Person owner and 1 Company owner. There would be a relationship between Company::CompanyID and Owners::CompanyProperty. In Owners, there would be two records: one in which PeopleOwner is set, and CompanyOwner is not, and one in which CompanyOwner is set, and PeopleOwner is not.

Yes, and in both those records, CompanyProperty would be set to the Company that is owned by the two (the person and the other company); while OtherProperty would be null in both.

Similarly, if I wanted to know what properties the Company owned, I would match Company::CompanyID and Owners::CompanyOwner. Each record would have either CompanyProperty set, pointing to an owned Company, or OtherProperty would be set, pointing to a different property database. Is this correct?

Exactly.

The second part of my question is, in the Name field for the portal showing the owners, how does it know that if PeopleOwner is is set, it must display People::FullName, and if CompanyOwner is set, it must use Company::CompanyName?

One way is to create a field Name in Owners file which is a calculation:

Case(

not IsEmpty(PeopleOwner), PEOPLEOWNER::FullName,

not IsEmpty(CompanyOwner), COMPANYOWNER::CompanyName,

"!Error!")

Then in the portal you can just put OWNERS::Name.