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、城市、州、邮政编码、国家等)).我最初打算将这些字段中的每一个都作为一个列包含在每个相关表中(例如，订单将包含地址 1/2/3、城市、州等.客户也将包含相同的列布局).但是我的一部分想将 DRY/标准化原则应用于这种情况，即有一个名为地址"的表，它通过相应表中的外键引用.
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....
)
从设计的角度来看，我喜欢这种方法，因为它创建了一种易于更改的标准地址格式，即如果我需要添加 Address4，我只会将它添加到一个地方而不是每个表.但是，我可以看到构建查询所需的 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屋！
