Very Computer

by **imani_technology_s..** » Wed, 03 Mar 2004 01:37:41

What is the best way to model a company, its parent companies, and its
affiliated companies? I have a company table with 2 self joins (one
for Parents and one for Affiliates). However, a Company might have
several Affiliates. Does that mean I should have a separate Affiliate
table?

by **Adam Machani** » Wed, 03 Mar 2004 01:47:45

Yes, modelling a many-to-many relationship requires a mapping table.

Quote:> What is the best way to model a company, its parent companies, and its
> affiliated companies? I have a company table with 2 self joins (one
> for Parents and one for Affiliates). However, a Company might have
> several Affiliates. Does that mean I should have a separate Affiliate
> table?

by **Joe Celk** » Wed, 03 Mar 2004 07:41:52

Quote:>> What is the best way to model a company, its parent companies, and

its affiliated companies? <<

Put the company information in one table and the structural information
in a second table. The second table will be (m:n) from your
description, should use the tax id numbers or other legal identifier and
contain information about the legal relationship (affilate, subsidary,
fully owned, x-% owned, privately held, etc.)

Beware of n-ary relationships that can lead to 5NF problems.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

by **Mike Lerc** » Wed, 03 Mar 2004 22:19:00

Quote:>What is the best way to model a company, its parent companies, and its
>affiliated companies? I have a company table with 2 self joins (one
>for Parents and one for Affiliates). However, a Company might have
>several Affiliates. Does that mean I should have a separate Affiliate
>table?

Give a look at the Data Model Resource book revised edition volume 1
by Len Silverston (ISBN 0-471-38023-7) for some thoughts on the
matter, though they might be more complex than what you're asking for.
Still thought provoking.

Lerch

Very Computer

Model for a company, its parents, and its affiliates

Model for a company, its parents, and its affiliates

Model for a company, its parents, and its affiliates

Model for a company, its parents, and its affiliates

Model for a company, its parents, and its affiliates