三个客户地址在一个表或单独的表中？

问题描述

在我的申请中，我有一个客户课程和地址课程。 Customer 类有 Address 类的三个实例： customerAddress ， deliveryAddress ， invoiceAddress 。

In my application I have a Customer class and an Address class. The Customer class has three instances of the Address class: customerAddress, deliveryAddress, invoiceAddress.

在数据库中反映此结构的最佳方式是什么？

• 直接的方式是客户表和单独的地址表。


• 更正规的方式只是每个地址都有列的客户表格（street示例：customer_street，delivery_street，invoice_street）

你的经验是什么？这些方法有什么优点和缺点吗？

What are your experiences with that? Are there any advantages and disadvantages of these approaches?

推荐答案

如果您100％确定客户只有3地址您描述然后这是好的：

If you are 100% certain that a customer will only ever have the 3 addresses you described then this is OK:

CREATE TABLE Customer
(
    ID int not null IDENTITY(1,1) PRIMARY KEY,
    Name varchar(60) not null,
    customerAddress int not null
        CONSTRAINT FK_Address1_AddressID FOREIGN KEY References Address(ID),
    deliveryAddress int null
            CONSTRAINT FK_Address2_AddressID FOREIGN KEY References Address(ID),
    invoiceAddress int null
            CONSTRAINT FK_Address3_AddressID FOREIGN KEY References Address(ID),
    -- etc
)

CREATE TABLE Address
(
    ID int not null IDENTITY(1,1) PRIMARY KEY,
    Street varchar(120) not null
    -- etc
)

否则我会这样建模：

CREATE TABLE Customer
(
    ID int not null IDENTITY(1,1) PRIMARY KEY,
    Name varchar(60) not null
    -- etc
)

CREATE TABLE Address
(
    ID int not null IDENTITY(1,1) PRIMARY KEY,
    CustomerID int not null
        CONSTRAINT FK_Customer_CustomerID FOREIGN KEY References Customer(ID),
    Street varchar(120) not null,
    AddressType int not null
    -- etc
)

这篇关于三个客户地址在一个表或单独的表中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！