Re: OO and relation "impedance mismatch"

From: Kenneth Downs <firstinit.lastname_at_lastnameplusfam.net>
Date: Thu, 07 Oct 2004 08:34:50 -0400
Message-ID: <b5d3kc.s8g.ln@mercury.downsfam.net>

Gene Wirchenko wrote:

> Kenneth Downs <firstinit.lastname_at_lastnameplusfam.net> wrote:
>
> [snip]
>

>>The current arbitrary feature for this case, which did not seem worth
>>mentioning in that post, is that you can specify a suffix to affix to the
>>columns in the child table.  So for two foreign keys to the same table one
>>is suffixed "_parent" and one suffixed "_child".  This makes me really
>>uneasy though, I keep thinking "salesperson1", "salesperson2", etc...


> 

>      Perfectly legitimate.  That is not a 1NF violation.  It may be

> bad design, but that is not necessarily the case.
>
> There could be
> Departments
> -----------
> ManagerEmpNbr
> SecretaryEmpNbr
> ...
> Both columns given could be FKs into an employee table.
>
> Or
> Games
> -----
> GameDate
> GameLocation
> HomeTeamID
> VisitingTeamID
>

Agreed on all examples. But the mechanism allows 2NF violations as well, with no way to distinguish between them. I have been asking myself if there is some way to allow the legitimate cases while preventing 2NF violations. The salesperson example I keep giving as a problem follows this pattern:

1. System created, sales orders have column "salesperson"
2. New customer 3 yrs later says they need a second salesperson column. Instead of breaking out the column to a child table, they add "salesperson2", at great cost and creating a de facto permanent support burden, since customer is always claiming it is not handled right and asking for some small tweak.
3. Another new customer says they need FOUR salesperson columns, and the whole thing becomes a crawling horror. The tweaks now never end, and because there are two customers, tweaking for one ticks off another.

The reason they did the 2NF violation at point 2 was simple, it was easier.

The only idea I've explored in thought experiments to any degree is the idea that if it were easier to maintain 2NF, more people would do it (or at least more people who work for me). Most frameworks are OK on creating a new child table and UI code for maintaining it, the trick seems to be getting that 1 column over to the child table during upgrade, and then making it look like nothing happened for people who don't need this mod. As you can imagine, I have not pursued that too much just yet, it is a lower order of problem.

-- 
Kenneth Downs
Use first initial plus last name at last name plus literal "fam.net" to
email me

Received on Thu Oct 07 2004 - 07:34:50 CDT