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

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

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

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

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

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