Обсуждение: Need sql to pull data from terribly architected table

On Tue, 23 Oct 2012 18:52:52 +0000, Gauthier, Dave said:
> Here's the deal...
>
> Instead of architecting and loading a table like...
> create teble foo (col1 text, col2 text, col3 text, col4 text, col5
> text); insert into foo (col1,col2,col3,col4,col5) values
> ('c1',null,'c3','c4',null);
>
> They did this instead...
>
> create table foo (property text, value text);
> insert into foo (property, value) values ('col1','c1'),
> ('col3','c3'), ('col4','c4');
>
> Notice how "col2" and "col5" were simply left out of the table in the
> 2nd model to indicate null.
>
> The question is how to do this model 1 query for model 2...
>
> select col1,col2 from foo where col4='c4' and col5 <> 'xxx';
>
> I know I have to use outer joins to deal with the potential of
> nulls.  But I don't know how to construct this.  I won't list my
> failed attempts (so as not to embarass myself :-))
>
> Thanks in Advance !


Hi Deve,

If it were me, I'd use a computer language like Perl, Python, Ruby, Lua
or Java to retrieve the data, rather than trying to do the whole thing
in SQL. Looking at the way they constructed their table, I'd guess the
intent was to use a language to do the logic, rather than pure SQL.

The way they constructed the table looks to me like they were trying to
comply with the first normal group -- no repeating columns (no arrays).
This would make sense if col1 through col5 were all the same type of
entity -- for instance, each is the name of a child of the couple. By
doing it as key-value pairs, each couple can have as many or as few
children as necessary, rather than reserving five columns for children
and then running into trouble when a six child family comes along.

Also, with the organization they're using, one can make new "columns"
on the fly. Years ago I created a litigation support database
structured partially as key-value pairs (along with a "row number" -- I
don't know how your database got along without a key to show which row
each key-value pair belonged to). Anyway, the keypuncher is punching
data, comes across a brand new type of data (let's say "artist"), so
for this row the keypuncher puts in a key-value pair of "artist=Lady
Gaga". From a practical point of view, data structure could be change
at key entry time, and needn't have been anticipated by the programmer
nor recompiled or reorganized when a new type of data element entered
the requirements.

I'll bet you dollars to donuts if you could speak to the original
programmer, he'd show you a good reason for his data organization, and
he'd also tell you he in no way anticipated that the data would ever be
handled purely by SQL.

Anyway, bottom line, a simple, procedural language with an interface to
Postgres would be a quick and easy way to convert this data to the type
you prefer.

HTH

SteveT

Steve Litt                *  http://www.troubleshooters.com/
                          *  http://twitter.com/stevelitt
Troubleshooting Training  *  Human Performance

On Thu, Oct 25, 2012 at 2:42 AM, Steve Litt <slitt@troubleshooters.com> wrote:
> Also, with the organization they're using, one can make new "columns"
> on the fly. ... Anyway, the keypuncher is punching
> data, comes across a brand new type of data (let's say "artist"), so
> for this row the keypuncher puts in a key-value pair of "artist=Lady
> Gaga". From a practical point of view, data structure could be change
> at key entry time, and needn't have been anticipated by the programmer
> nor recompiled or reorganized when a new type of data element entered
> the requirements.

That's wonderfully flexible, but it forfeits the protection that a
well-designed schema gives. A system like that is likely to end up
with different records storing the same data under slightly different
names, and you'll have a massive proliferation of "columns" that have
only a single row's value in them. That's fine if that's what you
want, but from a data entry standpoint, I think it's _too_ flexible
for most purposes.

ChrisA

On Thu, 25 Oct 2012 03:56:39 +1100, Chris Angelico said:
> On Thu, Oct 25, 2012 at 2:42 AM, Steve Litt
> <slitt@troubleshooters.com> wrote:
> > Also, with the organization they're using, one can make new
> > "columns" on the fly. ... Anyway, the keypuncher is punching
> > data, comes across a brand new type of data (let's say "artist"), so
> > for this row the keypuncher puts in a key-value pair of "artist=Lady
> > Gaga". From a practical point of view, data structure could be
> > change at key entry time, and needn't have been anticipated by the
> > programmer nor recompiled or reorganized when a new type of data
> > element entered the requirements.
>
> That's wonderfully flexible, but it forfeits the protection that a
> well-designed schema gives. A system like that is likely to end up
> with different records storing the same data under slightly different
> names, and you'll have a massive proliferation of "columns" that have
> only a single row's value in them. That's fine if that's what you
> want, but from a data entry standpoint, I think it's _too_ flexible
> for most purposes.

True enough.

In my particular case, my program was used by litigation support
professionals (I probably shouldn't have called them "keypunchers") who
knew what they were doing. Secondly, IIRC, I had a separate table for
field names, so that before creating a new field name, they'd be
presented with the current ones.

One possible way of limiting the possible damage you pointed out would
be to have only a select few be permitted to add new field names. I
think my program might have done that -- I seem to remember those
entering data having to call the supervisor to put in a new category of
information.

But yeah, the data organization I mentioned requires use by halfway
intelligent and competent users, and certainly isn't universally
appropriate.

Thanks

SteveT

Steve Litt                *  http://www.troubleshooters.com/
                          *  http://twitter.com/stevelitt
Troubleshooting Training  *  Human Performance