0

I've been struggling with how I should indicate that a certain record in a database is the "fallback" or default entry. I've also been struggling with how to reduce my problem to a simple problem statement. I'm going to have to provide an example.

Suppose that you are building a very simple shipping application. You'll take orders and will need to decide which warehouse to ship them from.

Let's say that you have a few cities that have their own dedicated warehouses*; if an order comes in from one of those cities, you'll ship from that city's warehouse. If an order comes in from any other city, you want to ship from a certain other warehouse. We'll call that certain other warehouse the fallback warehouse.

You might decide on a schema like this:

Warehouses
    WarehouseId
    Name

WarehouseCities
    WarehouseId
    CityName

The solution must enforce zero or one fallback warehouses.

You need a way to indicate which warehouse should be used if there aren't any warehouses specified for the city in question. If it really matters, you're doing this on SQL Server 2008.

EDIT: To be clear, all valid cities are NOT present in the WarehouseCities table. It is possible for an order to be received for a City not listed in WarehouseCities. In such a case, we need to be able to select the fallback warehouse.

If any number of default warehouses were allowed, or if I was assigning default warehouses to, say, states, I would use a DefaultWarehouse table. I could use such a table here, but I would need to limit it to exactly one row, which doesn't feel right.

How would you indicate the fallback warehouse?

*Of course, in this example we discount the possibility that there might be multiple cities with the same name. The country you are building this application for rigorously enforces a uniqueness constraint on all city names.

Eric
  • 18,512
  • 4
  • 29
  • 34
  • What version of SQL Server? 2008? – EBarr Jan 24 '12 at 01:56
  • I'm surprised that it matters. I was hoping to avoid an esoteric solution that requires a certain version of SQL Server; it doesn't feel like the sort of problem that would require exclusive functionality. That said, it's 2008. I'll update the tags and question. – Eric Jan 24 '12 at 02:03
  • I'm curious about the down vote and close vote. Person who marked me down: did you dislike the title or find that the question was unclear? – Eric Jan 24 '12 at 02:07
  • 1
    fyi ...i think it's a valid question for a common design problem in SQL - +1. Also, it's not esoteric, but i immediately though of filtered indexes which were added in sql 2008. – EBarr Jan 24 '12 at 02:12
  • @EBarr the platform is totally irrelevant to the problem. – davidmontoyago Jan 24 '12 at 02:12
  • 1
    @davidmontoyago - filtered indexes can be a quite relevant way of enforcing DRI/Uniqueness that in the past required triggers. We only got filtered index in sql 2008. – EBarr Jan 24 '12 at 02:13
  • 2
    @davidmontoyago - The platform may be irrelevant to the problem, but it is ***highly*** relevant to the solution. – RBarryYoung Jan 24 '12 at 03:38

3 Answers3

4

I understand your problem, but have questions about parts of it, so I'll be a bit more general.

  • If at all possible I would store warehouse/backup warehouse data with your inventory data (either directly hanging of warehouses, or if it's product specific off the inventory tables).
  • If the setup has to be calculated through your business logic then the records should hang off the order/order_item table

In terms how to implement the structure in SQL, I'll assume that all orders ship out of a single warehouse and that the shipping must be hung off the orders table (but the ideas should be applicable elsewhere):

  • The older way to enforce zero/one backup warehouses would be to hang a Warehouse_Source record of the Orders table and include an "IsPrimary" field or "ShippingPriority" then include a composite unique index that includes OrderID and IsPrimary/ShippingPriority.

  • if you will only ever have one backup warehouse you could add ShippingSource_WareHouseID and ShippingSource_Backup_WareHouseID fields to the order. Although, this isn't the route I would go.

In SQL 2008 and up we have the wonderful addition of Filtered Indexes. These allow you to add a WHERE clause to your index -- resulting in a more compact index. It also has the added benefit of allowing you to accomplish some things that could only be done through triggers in the past.

  • You could put a Unique filtered index on OrderID & IsPrimary/ShippingPriority (WHERE IsPrimary = 0).

Add a comment or such if you want me to explain further.

EBarr
  • 11,826
  • 7
  • 63
  • 85
  • I like the idea of a filtered index. Intuitively, it probably already makes sense that there is only one Primary warehouse. A filtered index doesn't seem very discoverable, but I can use it to help enforce something that should be intuitive. Thanks! – Eric Feb 26 '12 at 21:21
  • Filtered indexes are just as discoverable as more traditional unique indexes (sp_helpindex MyTable) and open up some great new ways to think about D.R.I. – EBarr Feb 26 '12 at 22:26
0

As I see it, the fallback warehouse after all is just another warehouse and if I understood, every record in WarehouseCities has a reference to one record in Warehouses:

WarehouseCities(*)...(1)Warehouses

Which means that if there are a hundred cities without a dedicated warehouse they all will reference the id of an specific fallback warehouse. So I don't see any problem (which makes me thing I didn't understand the problem), even the model looks well defined.

Now you could identify if a warehouse is fallback warehouse with an attribute like type_warehouse on Warehouses.

EDIT after comment

Assuming there is only one fallback warehouse for the cities not present in WarehouseCities, I suggest to keep the fallback warehouse as just another warehouse and keep its Id (WarehouseId) as an application parameter (a table for parameters maybe?), of course, this solution is programmatically and not attached to your database platform.

davidmontoyago
  • 1,834
  • 14
  • 18
  • The issue I explained poorly is that a given city may not be present in the WarehouseCities table. I want a solution that lets me pick the fallback warehouse if the city is not present in WarehouseCities. I'll update my question to make this clear. – Eric Jan 24 '12 at 15:44
0

re: how I should indicate that a certain record in a database is the "fallback" or default entry

use another column, isFallback, holding a binary value. I'm assuming your fallback warehouse won't have any cities associated with it.

Beth
  • 9,531
  • 1
  • 24
  • 43
  • The fallback warehouse might have cities associated with it, but that wouldn't impact the isFallback column's use. However, having an isFallback column does not enforce that, at most one warehouse may be marked as the fallback warehouse. – Eric Jan 24 '12 at 16:47
  • Then, like you said, you can create another table with your fallback warehouse ID and enforce RI. It seems like overkill, and it might be, but it satisfies your requirement. I don't think you're missing anything here. – Beth Jan 24 '12 at 16:50