Re: New arrivals (II)

Andy Burns said:
On 27/06/2007 22:34, Richard Robinson wrote:

Ah. That makes it less painful to think about, certainly; but it involves
duplicating a lot of data.
...
but I rather thought the idea was supposed to be Not How One Does
Things. But ... it does make it less painful).

if the keynames are text and significantly longer than an int (i.e 4 or
8 characters) you could have an intermediate keys/values table.

av. key length, 2.5 chars. values, 20ish (utf8).

ie more or less lost in the noise of the efficiency of the dabase-engine
itself, I'd reckon. It's just that I keep bumping into what looks like
Proper Design dogma, which maybe, being arj to this, I'm reading too much
into, except that the code seems to insist. Using the tools I am, I don't
think I can express this without an intermediate table. But there are other
advantages to using them ... I'm not sure it matters much, in the long run,
bar the headhurtingness of getting there.


Yebbut, that gives 1 row per keyval pair, for all keys, sorted on the name
of the key ?

well since they /are/ rows in the tables any query will return them as
multiple rows, not as one row of multiple columns, if you don't know at
design time what the keys.

Irritatingly, I do have a version of this sort-of-jbexvat, as a single row;
you can create arj columns at run time, once you see a key and notice you
don't have a column called that. It's just, the code makes it obvious it
thinks I'm fighting it, so I'm trying to see how to be nice to it instead
and Do It All Proper, Like. And suffering from the delusion that it
shouldn't be difficult once I realise <X>

let me check, you effectively want

SELECT keyval.value FROM item, keyval
Well, more kind of SELECT all item fields and all keyvalues pertaining to it
WHERE item.id = myitem
AND keyval.item_id = item.id
AND key = "mykey"
ORDER BY keyval.value;

but you want it as one row? You'll have to call the SQL from a higher
level language and build your "row" up manually for that

I think so. In fact, the best option looks like to back off. I realise, if I
do only the select(s) for the criteria I want to match, I get the resultset
object I want, to store and loop round doing further such sub-selects on as
convenient [1]; and if I do the sort separately, subsequently, on a
throw-away copy of it, I think (hope) the problem gets thrown away with it,
and however I bodge reading the values is at least a smaller, more local
bodge.

[1] Because this is all to be driven from an html form. I don't fancy
expressing arbitrarily-deeply-nested boolean combinations of criteria as a
easy-to-use-for-the-non-techy GUI, so the simple and friendly alternative is
Stepwise Refinement, pick one at a time and then add another one later.


And I always seem to end up settling for a list of item ids, and get the
values for each item by looping through them in perl, but I'm sure there
must be a way to do it all in one go. Well, I'd like it if there were,
anyway.

to use a list of ids [*] (so long as it has a reasonable limit) you can
use IN

Potentially/initially as many as there are items. I'm not sure of the limits
of this; the above looks more hopeful.

You can even have queries
involving multiple instances of the same table within one query but with
different aliases, but then your brian starts to jurt.

Some of my trial-and-errors were making that happen. I can see the principle,
but in practise; yes. It did.



--
Richard Robinson
"The whole plan hinged upon the natural curiosity of potatoes" - S. Lem

My email address is at http://www.qualmograph.org.uk/contact.html
.

Relevant Pages

  • Re: Word field Skip IF problem
    ... You can certainly make a field that SKIPs with multiple criteria, ... These are also the mergefields in my word docs. ... It basically says Skip record if "mergefield to" does not equal that ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Can Excel return multiple correct answers?
    ... If I'm reading the 2000 manual correct, it looks like I can have multiple ... And that's by putting the criteria on the SAME row? ... bunch in one column, and then do a V-Lookup, because there are multiple ...
    (microsoft.public.excel.worksheet.functions)
  • MATCH using multiple criteria?
    ... Is there a way to find the first row in a data set that meets multiple ... criteria in multiple columns. ...
    (microsoft.public.excel.programming)
  • RE: Matching Values
    ... The Advanced Filter command on the Data menu lets you use complex criteria ... Type Salesperson Sales ... Multiple criteria in multiple columns where all criteria must be true ...
    (microsoft.public.excel.misc)
  • RE: Extract & compare data on different worksheets
    ... The Advanced Filter command on the Data menu lets you use complex criteria ... Type Salesperson Sales ... Multiple criteria in multiple columns where all criteria must be true ...
    (microsoft.public.excel.misc)