SQL数据库设计最佳实践（地址） [英] SQL Database Design Best Practice (Addresses)
问题描述
当然，我意识到设计一个SQL数据库没有一个正确的方法，但是我想对我的特定情况有更好的或更差的一些意见。
Of course I realize that there's no one "right way" to design a SQL database, but I wanted to get some opinions on what is better or worse in my particular scenario.
目前，我正在设计一个订单输入模块（Windows .NET 4.0应用程序与SQL Server 2008），而在两个设计决策之间，当涉及到可以应用于多个位置的数据时，我就被撕毁了。在这个问题中，我将具体参考地址。
Currently, I'm designing an order entry module (Windows .NET 4.0 application with SQL Server 2008) and I'm torn between two design decisions when it comes to data that can be applied in more than one spot. In this question I'll refer specifically to Addresses.
地址可以被各种对象（订单，客户，员工，出货等）使用，而他们几乎总是包含相同的数据（地址1/2/3，城市，州，邮政编码，国家等）。我最初将这些字段中的每一个作为每个相关表格中的列（例如Orders将包含Address1 / 2/3，City，State等），并且客户还将包含相同的列布局）。但是，我的一部分想要将DRY / Normalization原则应用于这种情况，即有一个名为Addresses的表，通过外键引用到适当的表格中。
Addresses can be used by a variety of objects (orders, customers, employees, shipments, etc..) and they almost always contain the same data (Address1/2/3, City, State, Postal Code, Country, etc). I was originally going to include each of these fields as a column in each of the related tables (e.g. Orders will contain Address1/2/3, City, State, etc.. and Customers will also contain this same column layout). But a part of me wants to apply DRY/Normalization principles to this scenario, i.e. have a table called "Addresses" which is referenced via Foreign Key in the appropriate table.
CREATE TABLE DB.dbo.Addresses
(
Id INT
NOT NULL
IDENTITY(1, 1)
PRIMARY KEY
CHECK (Id > 0),
Address1 VARCHAR(120)
NOT NULL,
Address2 VARCHAR(120),
Address3 VARCHAR(120),
City VARCHAR(100)
NOT NULL,
State CHAR(2)
NOT NULL,
Country CHAR(2)
NOT NULL,
PostalCode VARCHAR(16)
NOT NULL
)
CREATE TABLE DB.dbo.Orders
(
Id INT
NOT NULL
IDENTITY(1000, 1)
PRIMARY KEY
CHECK (Id > 1000),
Address INT
CONSTRAINT fk_Orders_Address
FOREIGN KEY REFERENCES Addresses(Id)
CHECK (Address > 0)
NOT NULL,
-- other columns....
)
CREATE TABLE DB.dbo.Customers
(
Id INT
NOT NULL
IDENTITY(1000, 1)
PRIMARY KEY
CHECK (Id > 1000),
Address INT
CONSTRAINT fk_Customers_Address
FOREIGN KEY REFERENCES Addresses(Id)
CHECK (Address > 0)
NOT NULL,
-- other columns....
)
从设计的角度来看，我喜欢这种方法，因为它创建了一种易于更改的标准地址格式，即如果我需要添加地址4我将它添加到一个地方而不是每个表。但是，我可以看到构建查询所需的JOIN数量可能会有点疯狂。
From a design standpoint I like this approach because it creates a standard address format that is easily changeable, i.e. if I ever needed to add Address4 I would just add it in one place rather than to every table. However, I can see the number of JOINs required to build queries might get a little insane.
我想我只是想知道是否有任何企业级SQL架构师在那里曾经使用过这种方法，或者如果创建的JOIN数量会产生性能问题？
I guess I'm just wondering if any enterprise-level SQL architects out there have ever used this approach successfully, or if the number of JOINs that this creates would create a performance issue?
推荐答案
在正确的轨道上通过将地址分解成自己的表。我会添加几个其他建议。
You're on the right track by breaking address out into its own table. I'd add a couple of additional suggestions.
-
考虑将FK地址从客户/订单表中创建，并创建连接表代替。换句话说，将客户/地址和订单/地址视为设计中的多对多关系，以便将来可以轻松支持多个地址。是的，这意味着引入更多的表格和联接，但是您获得的灵活性是非常值得的。
Consider taking the Address FK columns out of the Customers/Orders tables and creating junction tables instead. In other words, treat Customers/Addresses and Orders/Addresses as many-to-many relationships in your design now so you can easily support multiple addresses in the future. Yes, this means introducing more tables and joins, but the flexibility you gain is well worth the effort.
考虑为城市，州和国家实体创建查找表。然后，地址表的城市/州/国家/地区列由指向这些查找表的FK组成。这样，您可以保证所有地址的拼写一致，并为您提供一个存储额外元数据（例如，城市人口）的地方，如果将来需要的话。
Consider creating lookup tables for city, state and country entities. The city/state/country columns of the address table then consist of FKs pointing to these lookup tables. This allows you to guarantee consistent spellings across all addresses and gives you a place to store additional metadata (e.g., city population) if needed in the future.
这篇关于SQL数据库设计最佳实践（地址）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
