Informix

Expand all | Collapse all

Modeling "Overrides"

  • 1.  Modeling "Overrides"

    Posted 9 days ago

    Let me start by saying that logical modeling has never been my strong suit. I've always been more of a physical infrastructure / back-end kind of guy. So I want to ask the peanut gallery how to solve a problem.

    What's the best way to model overrides in the database? You're probably wondering what I mean by 'overrides,' so I'll try to explain as best I can, oversimplifying for brevity.

    My current employer is a rental car broker. Suppose we have a standard rental car agreement that we send out to everyone who reserves with us. Now suppose that occasionally, for a certain location (e.g., Boston Logan Airport), we instead send out a different one. How would you model that in the database?

    The basic logic is, if I have a specific value associated with this location, use that; otherwise use this broader default.

    Now add a layer of complexity: a second "override." Let's call this one vendor. So let's say Enterprise Rent-A-Car has a particular rental agreement we need to use, while Avis, Hertz, Budget, etc., all use the standard one.

    Now the logic becomes, if I have an agreement associated with this specific vendor at this specific location, use that; otherwise, if I have an agreement associated with this specific vendor _or_ this specific location*, use that; otherwise, use the default ("standard") agreement.

    Converting that into database terms, I want a model that allows me to run a query that says "here's the location and the vendor, give me the correct rental agreement" and incorporates the above logic. And what I don't want to do is just hard code the default.

    In real life, it's even more complicated than that: there are about five or six layers of possible override, each adding an order of magnitude of additional complexity. But let's just start with the two to get the concepts down.

    Outside-the-box thinking is welcome of course.

    TIA,

    -TJG



    ------------------------------
    TOM GIRSCH
    ------------------------------


  • 2.  RE: Modeling "Overrides"

    Posted 9 days ago
    Edited by TOM GIRSCH 9 days ago
    * - What happens if I have someone who tries to rent at Enterprise at Logan and I have an override record for all Enterprise rentals and another override record for all Boston Logan rentals, but no specific record for Enterprise at Logan? That's an excellent question!

    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 3.  RE: Modeling "Overrides"

    Posted 9 days ago
    Conceptually it's very easy - first you select a template that matches the criteria (or skip this if template has always same number of fields) then for each field in the template you select item with latest timestamp (modification time) that also matches your generated filter. Conflict resolution is entirely up to you - if Enterprise dictates one value and Logan another - you can either select one with latest timestamp or complicate matters and add a priority attribute. I personally favor simple timestamps because it allows to generate content which was valid at (before) specific date.

    ------------------------------
    Vladimir Kolobrodov
    ------------------------------



  • 4.  RE: Modeling "Overrides"

    Posted 9 days ago

    Thanks for responding. Got an example of how that would look in [pseudo]-SQL?






  • 5.  RE: Modeling "Overrides"

    Posted 8 days ago
    I would not want to take all of the fun away from you!

    Still, here is a fragment of something that I wrote few years ago for the scenario similar to one you described - modifying (or, in your terms "overriding")  configuration depending on the hardware platform and, in some cases, exact host name:

    ...............
    while read cftarget cftname
    do
        confclause="ttype = $ttid and cfcode = $cfcode and cftarget = $cftarget"
        stmt="select unique cfkey from configuration where $confclause;"
        keys=`echo "$stmt"|($xres) 2>&1|sed s/'No rows found'/'0 row(s) retrieved'/|sed -n -e '/cfkey/,/retrieved/p'|\
        egrep -v 'cfkey|retrieved' | awk 'NF>0'`
        [ -z "$keys" ] && echo " - no records found" && continue
        basestmt="select FIRST 1 cfplatform,cfhost,cfdate,cftype,cfval from configuration where ( $confclause ) and ( $platclause ) and ( $hostclause )"

        for k in $keys
        do
             stmt="$basestmt and cfkey = \"$k\" order by cfdate desc;"
             strbuf=`echo "$stmt"|($xres) 2>&1`
    .....



    ------------------------------
    Vladimir Kolobrodov
    ------------------------------



  • 6.  RE: Modeling "Overrides"

    Posted 6 days ago

    That's similar to what we're already doing. Storing multiple records in the same table, using OR to catch the default and the override and doing a SELECT FIRST 1 with an ORDER BY to give us the highest priority first. I was hoping for something a bit less inelegant, but maybe there's no such solution.






  • 7.  RE: Modeling "Overrides"

    Posted 6 days ago
    If you find more elegant solution - please share. Good luck!

    ------------------------------
    Vladimir Kolobrodov
    ------------------------------