Re: how the heck do I model this?

1. Love that thread title!
2. >> Is this making sense? <<

I am getting lost in the description and would like to see some DDL andf code.

3) e.g., get the site_datatype_id/interval pair that occurs most frequently (i.e, in the most number of rows) where source_id = <x> and
((key = <key1> and key_value = <keyvalue1>) OR ... OR (key = <keyn> and
key_value = <keyvaluen>))

I can do that one with a modified relational division, I think.

4) >> ..for the odd lookup I need to do, the primary key is composed of a variable, and unknown until run-time, number of elements. <<

I think the problem is that what you are calling a key is not a key. It is a "best match" score that might have ties or fail completely. If you had Full SQL-92, you could do row comparison as well as scalar comparisons, but most products are not that good yet.

How about we find the source with the most hits? Something like this:

SELECT F1.source_id, COUNT (*)
FROM Foobar AS F1
WHERE source_id = :x
AND EXISTS
(SELECT *

FROM Parmlist AS P
WHERE P1.foo_key = F1.foo_key
AND P1.foo_key_value = F1.foo_key_value) GROUP BY F1.source_id;

Oh, there is no such thing as a "datatype_id" becuase data types are meta data and NEVER appear in a data base. Likewise, key is reserved word that describes how a data element is used, not what it is -- all names shoudl tell us what the data element is in the real world the model represents.

I think that you might have a seriously screwed up design just from the names. Received on Tue Dec 14 2004 - 00:15:38 CET