Re: how the heck do I model this?

"Carol M" <carol_marra_at_msn.com> wrote in message news:7d34edda.0411241329.56785d16_at_posting.google.com...
> Hope I can explain this... And I'll be talking more implementation...
> my theory language isn't real strong ...
>
> I have the following case:
>
> Based on a source_id plus n key/value pairs, where n is unknown (and
> theoretically unlimited), I need to look up 2 more values (let's call
> them site_datatype_id and interval). Obviously, the problem is that
> the 2 values to be looked up depend on multiple rows of lookup-data.
>
> Take the following mapping table. It more accurately models the true
> relationship of the elements (which is a 1:m relationship between
> source/ site_datatype/interval and key/value). (I know it's not
> normalized: source, site_datatype, and interval are repeating in this
> design -- this is really probably a view on a couple of tables):
>
> *source_id
> *site_datatype_id
> *interval
> *key
> key_value
>
> It's easy to lookup a key_value based on PK of source, site_datatype,
> interval,
> and key (and it is a requirement to go in that direction as well as
> the one described above). However, getting the site_datatype_id and
> interval from the source and multiple key/value pairs is not so
> straightforward... at least not that I can see. I'm sure that some
> sort of procedural kludge could be implemented to give the desired
> answer in this direction (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>))
>
> Is this making sense? Does anyone have any suggestions on how to
> handle such a case? Essentially, for the odd lookup I need to do, the
> primary key is composed of a variable, and unknown until run-time,
> number of elements.
>
> Thanks,
> Carol

I am not certain that I completely understand the problem, but the kind of problem you describe is usually the result of an incorrect relational design. If the value of a PK (or any part of a composite PK) is unknown, then it cannot be a PK. But, as I said, I am not sure. What do _you_ mean by "runtime"? Received on Fri Nov 26 2004 - 14:26:35 CET