Re: Code tables, conditional foreign keys?

[Benjamin Smith <lists@xxxxxxxxxxxxxxxxxx>]

"A deep unwavering belief is a sure sign that you're missing something."

                                                            -- Unknown

I had no intention of sparking an ideological discussion.

I read Joe's article reference previously - a simple case for using a normalized database. I would pretty much agree with his assessment from beginning to end. However, he really doesn't address my scenario at all. I'm not trying to mash my database together into a single table, I'm trying to deal with the fact that we have hundreds of tables with nearly identical syntax, but further, deal with the concept of "code tables".

See our product has to work in many venues, and each venue has their own set of code-table data that they'd like to support. Worse, they often use similar values for the different things, so the "natural key" is just not natural. Sometimes I've seen venues "re-using" the old code table value from previous years to mean new things in current/future years. Yes, this is a bad, bad, bad idea but it was still there and it's still my job to deal with it.

Surrogate keys are used to make sure that 15 to mean "BS College Degree" in venue A aren't confused with 15 to mean "No High School Education" in another venue. They cover a similar value, EG: applicant's educational level. Some values don't translate at all, (EG: differing representations of vocational arts) so using our own code table set and then translating doesn't work consistently, either.

So we have multiple, distinct sets of data to be used within a single field. Either that, or we create massive data tables with every possible different set of otherwise similar data, each of which has a foreign key to a table with a slightly different name, which is, far and away, even uglier. (EG: applicants.ca_edlevel, applicants.or_edlevel applicants.nv_edlevel, applicants.southca_edlevel...)

educational level is one example, there are hundreds that we have to deal with!

So.... back to the first question: is there a way to have a conditional foreign key?

On Saturday 23 May 2009 17:22:36 Lew wrote:
> Conrad Lender wrote:
> > I didn't intend any disrespect to Joe Celko. I have read a number of his
> > articles, which tend to be well written and informative. Last year, when
> > I posted to comp.databases asking for advice on whether to refactor that
> > table, he wrote "You will have to throw it all out and start over with a
> > relational design", "Throw away the idiot who did the EAV. This is not a
> > good design -- in fact, it is not a design at all", and "This is basic
> > stuff!!" Then he copied the same EAV example that was linked earlier by
> > Rodrigo, claiming that "someone like me" had suggested it. With all the
> > respect I have for Mr. Celko, that was hardly helpful, as that example
> > and the situation I had described were quite different. It also did not
> > encourage me to follow his advice and start from scratch (and fire my
> > boss, who was the mentioned "idiot").
>
> If we fired every boss who actually is an idiot there would be about half
> the number of bosses.
>
> All kidding aside, why is the boss specifying a database architecture?
> That is not the boss's job.
>
> > I understand the problems that can arise from bad design choices, and I
> > know that Celko is vehemently opposed to anything that resembles EAV,
>
> For good reasons.
>
> > but I felt that in our case "throwing it all away" would be excessive.
>
> Perhaps not. I had a situation some years ago where a supervisor would not
> let me normalize a database and consequently the project nearly failed.
> Fortunately, the company assigned a new team lead/project manager who did
> the normalization or it would have been a disaster. Trying to make a bad
> approach work is often, if not always, more expensive than replacing it
> with a good approach.
>
> > We had safeguards to ensure referential integrity, and keeping the
> > values in the same table allowed us to let users manage them all with
> > the same form. So I guess it's like Stefan Keller said in a different
> > thread today: "Know when to break the rules."
>
> Managing all the values in the same form is not intrinsically connected to
> whether one stores the values in an EAV layout.
>
> Telling oneself that one should know when to break the rules is not the
> same as knowing when to break the rules. They are the rules for good
> reason.
>
> All I'm saying is that EAV is a very problematic approach. I've been on
> projects that tried to use it, and while that didn't make me an expert on
> the matter by any means, it gave me some cause to trust Mr. Celko's opinion
> on the matter.
>
> --
> Lew
>
> --
> Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.