Entity relationship / integrity question

wcravens · #1 (**permalink**) 12-13-09, 13:50

I would appreciate any advice I can get for my simple modeling problem.

There is a set of properties. Each property must have an owner. Owners can be either companies or individuals. Owners can own more than one property.

The best integrity I've been able to come up with is the ability to make sure that the application cannot delete companies or individuals if they are currently property owners. However I have to have an 'owner_type' ENUM field so that the application knows how to get further details. I just wanted to run this past other people because I have a hunch that I am being blind to an alternative approach.

I've grossly over simplified my description and example implementation of the problem to illustrate just the relationship model I'm struggling with.

If there is a name for the relationship problem I've described I'd love to know that too.

Thanks for any help.

Code:

CREATE TABLE valid_id (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT
);

CREATE TABLE company (
  id INT NOT NULL,
  ....
  CONSTRAINT FOREIGN KEY (id) REFERENCES valid_id(id);
) 

CREATE TABLE person (
  id INT NOT NULL,
  ...
  CONSTRAINT FOREIGN KEY (id) REFERENCES valid_id(id);
) 

CREATE TABLE property_owner_account (
  account_id INT NOT NULL PRIMARY KEY,
  owner_type ENUM ('PERSON','COMPANY') NOT NULL,
  owner_id INT NOT NULL,
  ...
  CONSTRAINT FOREIGN KEY owner_id REFERENCES valid_id(id);
  CONSTRAINT UNIQUE owner_id; # Owners should only have 1 account
)

CREATE TABLE property (
  ...
  property_owner_account_id INT NOT NULL,
  CONSTRAINT FOREIGN KEY property_owner_account_id REFERENCES property_owner_account(owner_id);
)

MarkATrombley · #2 (**permalink**) 12-13-09, 21:09

That is certainly one way to do it. I would like to point out that property can be owned by more than one person (multiple names can be on a deed). Even if the property is owned by a single company/person there may be a lender that has an interest in the property. Not sure if you need to take that into account.

I tend to make a LegalEntity table similar to your property_owner_account table to provide a unique ID for each legal entity involved (company, person, government, etc.) then build separate tables for each type to hold the attributes of that specific type of legal entity. I usually use the same key, so the LegalEntity key is the key to the Person table, the key to the Company table, etc.

wcravens · #3 (**permalink**) 12-13-09, 21:28

Quote:

Originally Posted by MarkATrombley

That is certainly one way to do it.

Then I'm not entirely crazy.

Quote:

I would like to point out that property can be owned by more than one person

Yes - In my actual system there is an other property_co_owner link table. The main member account is for primary owner billing purposes, so I'm not sending out invoices to all 'owners'. Also in my case there can be co-owners that do not co-own all properties associated with a certain member account.

Quote:

I tend to make a LegalEntity table

Your LegalEntity table is the same as my 'valid_id' table in the example. LegalEntity is a much better name so I'm going to use that... (well legal_entity of course).

Thanks for your help Mark.

MarkATrombley · #4 (**permalink**) 12-13-09, 22:21

Sounds like you are in good shape then.

Glad I could help.

Internet Services

Web Development

Digital Marketing

Consumer Tech