
www.Usenet.com
| <-- __Chronological__ --> | <-- __Thread__ --> |
before i go flapping my gums and get deeper in alligators, wondering whether the following makes sense. i have a database which provides picklists for input. it started out with a 2 column key in the cross-reference table to the values table, call them company and state. it's a transitive closure. along the way, naturally, more columns have been added, call them region and division. so that now, the unique identifier from the cross-reference to the values would be: company + division + region + state. the transitive closure just got a whole lot bigger. my colleagues are complaining. they want to have a "default" row, which is read, then make an attempt to read on the full key values, which if it fails, use the previously read default. i don't like this, since the backend accepts the data blindly, on the assumption that it has been pre-edited. the pick-list values are modifiable in real-time, so i object to doing multiple selects to derive these pre-edited values. they claim, which may be true, that, while possible, there is rarely in fact any difference among the pick-lists for a given column based upon the values in the cross-reference key. so, they don't like having to maintain the TC. i'm not even sure that the syntax is permited, which (psuedo-codely) i think is this: select * from CrossReference as cr where cr.blah = default join Values vl on cr.vlId = vl.id UNION select * from CrossReference as cr2 where cr2.blah = specific join Values vl2 on cr2.vlId = vl2.id in most cases, the second select is expected to fail. i can't find any explanation in my reference material whether one can still get a result set, nor whether join-union-join is permited. this seems a reasonable way to implement pick-lists. has anyone done it this way? thanks, robert
| <-- __Chronological__ --> | <-- __Thread__ --> |