指示数据库中的主要/默认记录 [英] Indicating primary/default record in database

问题描述

我一直在努力，我应该如何表明数据库中的某个记录是后备或默认条目。我也一直在努力，如何减少我的问题，一个简单的问题陈述。我将提供一个例子。

假设你正在构建一个非常简单的送货应用程序。

我们假设你有几个城市有自己的专用仓库*。如果一个订单来自这些城市之一，您将从该城市的仓库发货。如果订单来自任何其他城市，您想从某个其他仓库发货。

您可以决定这样的模式：

 仓库
 WarehouseId 
名称
 
 WarehouseCities 
 WarehouseId 
 CityName 
  
 
 
 
解决方案必须强制执行零个或一个后备仓库。
 
 
 
如果没有为指定的城市指定任何仓库，应使用该仓库。 
 
 
 
编辑：为了清楚起见，所有有效的城市都不存在于WarehouseCities表中。对于未在WarehouseCities中列出的城市，可能会收到订单。在这种情况下，我们需要能够选择后备仓库。
 
 
 
如果允许任何数量的默认仓库，或者如果我将默认仓库分配给，状态，我会使用DefaultWarehouse表。我可以在这里使用这样的表，但我需要将其限制到正好一行，这是不正确的。 
 
 
 
如何表示后备仓库？ 
 
 
 
 
 
 
  *当然，在这个例子中，我们可能会有多个城市有相同的名称。您正在建立此应用程式的国家/地区严格执行所有城市名称的唯一性限制。 
解决方案
 
 
 
 
 
 
如果可能的话，我会存储仓库/备份仓库数据与您的库存数据（直接挂仓库，或如果它是库存表的产品特定）。
 
 
如果设置必须通过业务逻辑计算，记录应挂起order / order_item表
 
 
 
 
 
关于如何在SQL中实现结构，我假设所有订单并且运输必须挂在订单表（但是其他方面应该适用）：
 
 
 
 
 
 
执行零/一个备份仓库的旧方法是挂起Orders表的Warehouse_Source记录，并包括一个IsPrimary字段或ShippingPriority，然后包括一个包含OrderID和IsPrimary / ShippingPriority的复合唯一索引。 
 
 
 
 
如果您只有一个备份仓库，您可以在订单中添加ShippingSource_WareHouseID和ShippingSource_Backup_WareHouseID字段。虽然，这不是我会去的路线。
 
 
 
 
 
 
在SQL 2008和更高版本中， 已过滤的索引。这些允许您向索引中添加WHERE子句，从而获得更紧凑的索引。它还有额外的好处，允许你完成一些只能通过触发器在过去做的事情。 
 
 
 
 
 
您可以在OrderID& 
如果您希望我进一步解释，请添加评论。
 
 
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.
解决方案
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.

                        
这篇关于指示数据库中的主要/默认记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！