| Subject | RE: [IB-Architect] Circular Foreign Key Dependencies |
|---|---|
| Author | David Berg |
| Post date | 2001-03-27T17:35:49Z |
My view is outlaw circular dependencies. They're a pain. Consider:
1) They make initial table loads and restoring data very difficult.
2) They make deleting records very difficult.
Consider the relationship Jim suggested:
People: Reference Company
Company: Primary Contact references people
Since the primary contact for a company will almost always work for the
company, you can't delete the company without deleting the people in it, and
you can't delete the people in it without setting the primary contact to
NULL (which may not be a valid operation either)!
The solution is to use more than two tables to model the relationships.
There are several choices, two obvious ones are:
1) People
Company -> Primary Contact
CompanyPeople - Which people work for which companies
2) Company
People -> Company
PrimaryContacts -> Company, -> People
Of course, these can change the relationships (depending on which fields are
unique keys), but that's not necessarilly a bad thing.
Solution 1 could allow a person to work for more than one company (e.g. real
life). Solution 2 could allow more than one primary contact per company,
which would allow for different types of primary contacts (sales, marketing,
technical, etc).
A more difficult case is the exploding parts list and various other
outlines. These can be handled by putting the information in one table and
the relationships in another.
On the surface, this makes handling the tables seem much more complex;
however, there's no reason you can't build (updatable) views that make the
tables seem like a single table. Except for the limitations of the engine
to support updatable views... but that might be a better place to focus
efforts on improving the engine than on improving circular reference support
(which will always be a pain).
Dave Berg
DaveBerg@...
-----Original Message-----
From: Jim Starkey [mailto:jas@...]
Sent: Sunday, March 25, 2001 10:20 AM
To: IB-Architect@yahoogroups.com; IB-Architect@yahoogroups.com
Subject: Re: [IB-Architect] Circular Foreign Key Dependencies
At 11:46 AM 3/25/01 +0000, Glebas Paulevicius wrote:
for company, and companies has field primary_contact which
is a foreign key for people. Utterly natural. Also living
testimony against foreign key enforcement.
It's a must for systems that depend on foreign key declarations
for semi-automatic database generation. The Netfrastructure
base application, for example, using foreign key declarations
to generate html links when displaying fields which are part
of foreign keys. A neat hack.
software knows about your data, the more it can do to make
your life better.
of telling you that you screwed up your application design.
Circular dependencies seems to be a sympton of doing things
right.
hate to lose it over something as silly as neglect of a
committee to define socially responsible syntax. The traditional
Interbase (I) philosophy of "SQL -- don't fix it, don't improve
it, just implement it" was well and good as long as there was
an alternative for mature adults (e.g. GDML), but it doesn't
fly in this ago. Maybe the new mantra should be "SQL -- live
free or die".
Jim Starkey
To unsubscribe from this group, send an email to:
IB-Architect-unsubscribe@onelist.com
Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
1) They make initial table loads and restoring data very difficult.
2) They make deleting records very difficult.
Consider the relationship Jim suggested:
People: Reference Company
Company: Primary Contact references people
Since the primary contact for a company will almost always work for the
company, you can't delete the company without deleting the people in it, and
you can't delete the people in it without setting the primary contact to
NULL (which may not be a valid operation either)!
The solution is to use more than two tables to model the relationships.
There are several choices, two obvious ones are:
1) People
Company -> Primary Contact
CompanyPeople - Which people work for which companies
2) Company
People -> Company
PrimaryContacts -> Company, -> People
Of course, these can change the relationships (depending on which fields are
unique keys), but that's not necessarilly a bad thing.
Solution 1 could allow a person to work for more than one company (e.g. real
life). Solution 2 could allow more than one primary contact per company,
which would allow for different types of primary contacts (sales, marketing,
technical, etc).
A more difficult case is the exploding parts list and various other
outlines. These can be handled by putting the information in one table and
the relationships in another.
On the surface, this makes handling the tables seem much more complex;
however, there's no reason you can't build (updatable) views that make the
tables seem like a single table. Except for the limitations of the engine
to support updatable views... but that might be a better place to focus
efforts on improving the engine than on improving circular reference support
(which will always be a pain).
Dave Berg
DaveBerg@...
-----Original Message-----
From: Jim Starkey [mailto:jas@...]
Sent: Sunday, March 25, 2001 10:20 AM
To: IB-Architect@yahoogroups.com; IB-Architect@yahoogroups.com
Subject: Re: [IB-Architect] Circular Foreign Key Dependencies
At 11:46 AM 3/25/01 +0000, Glebas Paulevicius wrote:
>At 14:27 2001.03.24 +1100, you wrote:Two tables, companies and people. People has a foreign key
>>> Circular foreign key dependencies are natural and
>>> useful (contentious statement #1),
>
>Can you provide a one good example to illustrate where
>to have a circular reference is natural and "a must"?
>
for company, and companies has field primary_contact which
is a foreign key for people. Utterly natural. Also living
testimony against foreign key enforcement.
It's a must for systems that depend on foreign key declarations
for semi-automatic database generation. The Netfrastructure
base application, for example, using foreign key declarations
to generate html links when displaying fields which are part
of foreign keys. A neat hack.
>I never used circular reference in my life and never feltYup. Let your hair down and live a little. The more system
>a need for that. I agree, maybe due to limited experience. :)
>
software knows about your data, the more it can do to make
your life better.
>Sounds like the must-need for multiple inheritance in C++,Quite different. Multiple inheritence is mother nature way
>which caused a non-stop theoretical battle, and later, Java
>could live without it, though introducing interfaces.
>
of telling you that you screwed up your application design.
Circular dependencies seems to be a sympton of doing things
right.
>>Among the solutions:I'm really very fond of the "... references people" syntax --
>>
>> 1. alter table blah drop foreign key (seg1, seg2)
>
>The rules of thumb, I am using:
>
>1) Never use unnamed constraints;
>2) Drop constraint first, drop table last;
>
hate to lose it over something as silly as neglect of a
committee to define socially responsible syntax. The traditional
Interbase (I) philosophy of "SQL -- don't fix it, don't improve
it, just implement it" was well and good as long as there was
an alternative for mature adults (e.g. GDML), but it doesn't
fly in this ago. Maybe the new mantra should be "SQL -- live
free or die".
Jim Starkey
To unsubscribe from this group, send an email to:
IB-Architect-unsubscribe@onelist.com
Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/