Model for a company, its parents, and its affiliates

Model for a company, its parents, and its affiliates

Post 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?
 
 
 

Model for a company, its parents, and its affiliates

Post 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?


 
 
 

Model for a company, its parents, and its affiliates

Post 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!

 
 
 

Model for a company, its parents, and its affiliates

Post 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

 
 
 

1. Extracting Parent Model from Nested Sets Model

As i had seen an outer join as the only suggestion out there i
wondered if there werent a faster query for tables with a lot of
nested sets.

This is for mysql, but seems to be translatable to other dbs(?):




     THEN right+1

   END, name
   FROM categories
   ORDER BY left;

Nuncanada

2. Convert VB Function to T-SQL

3. Jbase parent company IPO?

4. Please Help with SQL

5. Parent-Child Modeling Problem

6. CFP ECOOP94 Workshop "Common Elements for Object-Oriented Programming S

7. Extract parent node from CELKO nested set model

8. US-GEAT0702-POSITIONS IN ORACLE APPS WITH A GE AFFILIATE

9. looking for e-commerce which can handle affiliate program

10. Popular affiliates query problem

11. Parent Dimension And Parent Member Can Change.