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
Thanks for responding. Got an example of how that would look in [pseudo]-SQL?
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.