How to relate fields in the same table

[kraasty]

I have a table with the data of some companies (fields > Comp_name,
Comp_address , Comp_city,Comp_tel, Comp_fax...)
I need to have a way of knowing that Companies named B and C are
doughter companies of company named A .

Sould I create a junction table with the fields: Mother_comp
and Doughter_comp and relate the fields to Comp_ID in the
Companies table,
or create a new field in the companies table and make a one-to-many
relationship with the ID field on the same table?

Is there a better way to relate fields in the same table?

I imagine that this is not a rare situation. Can you suggest any better
way to handle this (or at least what I should NOT do)?

Thank you
Andreas

 

[John Vinson]

I have a table with the data of some companies (fields > Comp_name,
Comp_address , Comp_city,Comp_tel, Comp_fax...)

You should certainly have a Primary Key field, a CompanyID. Names are
not unique. This could be an Autonumber, or a corporate TaxID if you
have it, or something - but don't trust the name!

I need to have a way of knowing that Companies named B and C are
doughter companies of company named A .

Sould I create a junction table with the fields: Mother_comp
and Doughter_comp and relate the fields to Comp_ID in the
Companies table,
or create a new field in the companies table and make a one-to-many
relationship with the ID field on the same table?

Either one works. The latter allows for purely heirarchical
relationships - a company can have one and only one parent, but a
parent can have multiple daughters. The junction table allows for more
complex arrangements such as two companies in partnership owning a
third.

Is there a better way to relate fields in the same table?

I imagine that this is not a rare situation. Can you suggest any better
way to handle this (or at least what I should NOT do)?

There are *reams* of theoretical writing about modeling heirarchies in
a relational database. Many recommend using (to me rather arcane) set
membership theory with left-joined trees. Do a web search for "Celko"
(Joe Celko, the maestro of theoretical query design) and "heirarchy"
for examples.

John W. Vinson[MVP]

 

[kraasty]

You should certainly have a Primary Key field, a CompanyID. Names are
not unique. This could be an Autonumber, or a corporate TaxID if you
have it, or something - but don't trust the name!

Either one works. The latter allows for purely heirarchical
relationships - a company can have one and only one parent, but a
parent can have multiple daughters.

Since the option without the junction table works and covers our needs,
I will use it for the mother/doughter company situation I described.

The junction table allows for more
complex arrangements such as two companies in partnership owning a
third.

You also gave me an idea how try approach the next question that I was
going to ask the group:
The case that a company that has a few product branches which are being
handled by different advertising companies in the same table, (and each of
the advertizers may handle many other products) If I'm not wrong, a
junction table could do the job in this case, and I will try that next.

There are *reams* of theoretical writing about modeling heirarchies in
a relational database. Many recommend using (to me rather arcane) set
membership theory with left-joined trees. Do a web search for "Celko"
(Joe Celko, the maestro of theoretical query design) and "heirarchy"
for examples.

John W. Vinson[MVP]

Thanks for the answer and the info for "Celco". I was afraid that maybe
one of these two ways was a bit unorthodox and I would end up with
problems in the future with data losses.

Andreas

 

[jcelko212]

I have a new books entitled TREES & HIERARCHIES IN SQL that you might
want to buy.

--CELKO--

 

[John Vinson]

I have a new books entitled TREES & HIERARCHIES IN SQL that you might
want to buy.

--CELKO--

Hi Joe! Haven't seen you in the newsgroups in a while.

Folks, if you're not familiar with his work, everything I've seen by
Mr. Celko is worth reading. It's sometimes a fair bit of work but I've
never come away without learning something useful!

John W. Vinson[MVP]