At 10:35 AM 3/27/01 -0700, David Berg wrote:

Thanks for the thoughtful reply. I think I agree with most
of your arguments, but not necessary with your conclusions.
I don't dispute your logic, but I tend to weight things
differently.

>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.
>

Agreed on both points. But they make many more things a great
deal easier. Yes, you could recompose two primitive tables
into a view, but that's seems like cheating (mostly because
it weakens my argument). I'm less inclined to lose sleep
over deletion issues because:

1. Disks are so cheap it seems pointless to delete
potentially useful information.

2. Bankrupcies and firings are always difficult, so
database maintenance accurate mirrors reality.

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

Yup, that is the problem. A properly layered backup and restore
(like GBAK) is a pain unless foreign key declarations are
deferred.

>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
>
>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).
>

First, if somebody works for two companies, you probably want to treat
him/her as two people for perfectly good reasons -- the phone number,
the addresses, and the business relationships will all be different.
And if you can him at the wrong job, he might get fired (see above).

Second, primary contact mean primary contact. Two primary contacts
means you're dealing with a committee, a government agency, or a
battery.

Third, this strikes me as needless generality. How much generality
is required in an application? Just enough; never too much. Implemented
a complex structure when a more simple one would suffice often leads
to more expensive, more cumbersome systems. And as every programmer
over 30 (a tiny minority, I'm afraid) has learned, hooks are almost
always in the wrong place and usually make unexpected changes more
difficult for no gain. If you're writing a HR system, write an HR
system. Let the feds worry about social security.

>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.
>

Ugh. Recursion is mother nature's way of telling you you should have
placed out of Bit 101. Firebird has a loverly schema to support
recursive system with GPRE support that leaves on breathless. Much
wasted on Borland.

>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).
>

Hey, there aren't any limitation on updatable views. Drop into raw
BLR and Bob's your uncle. OK, there could be a nicer language. Has
anyone thought of embedding a Java virtual machine...


Jim Starkey