如何设计映射表? [英] How to design a mapping table?

问题描述

我这里有一个设计问题.我在这方面是个新手，因此我需要一些帮助. 由于公司合并，一切都必须放在一个系统中；我应该用另一家公司的新客户ID来映射我们的客户.

Im having a bit of a design issue here. Im kind of a novice in this, so I need some help. Due to a company merge, where everything must go in one of the systems; Im supposed to map our customers with a new customerid in the other company.

当我获得新的customerID时，我应该确保它是唯一的，并且与我们现有的customerID相同.

When I get the new customerID's Im supposed to ensure that it is unique and the same goes for our existing customerID.

Current customerID: CurCustID
New customer ID:    NewCustID

首先，我希望数据库确保CurCustID列中的每个CurCustID都是唯一的-仅具有一条记录，其次，我希望NewCustID列是唯一的-仅具有一条记录. 第三，我希望CurCustID和NewCustID的行组合仅接受唯一数据.

First, I would like the database to make sure that every CurCustID in column CurCustID is unique - only with one record, secondly I would like the column NewCustID to be unique - only with one record. Third I would like that the row combination of CurCustID and NewCustID only accepts unique data.

如果您能帮助我，我将非常感激，但是如果我的做法不好，并且有最佳做法，请告诉我.

If you can help me I would be very thankful, on the otherhand if my approach is bad practice and there is a best practice way of doing this, then please let me know.

USE [Database]
GO

/****** Object:  Table [dbo].[TblMapning]    Script Date: 05/30/2016 14:30:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[TblMapning](
[CurCustID] [varchar](255) NOT NULL,
[NewCustID] [varchar](255) NOT NULL,
PRIMARY KEY CLUSTERED 
(
[CurCustID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY =     OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

 GO

SET ANSI_PADDING OFF
GO

推荐答案

似乎您必须创建3个单独的表才能实施所有这些操作.新客户ID在新表和映射表中的生成方式可以自动进行，具体取决于您的房屋规则来分配新客户ID.您可能想要创建代码或SP，以使该过程更加人性化，吐出错误等，以防止出现过时的旧年纪，但在表级别，这将是一种实施它的方法.

Seems like you'd have to create 3 separate tables to enforce all those things. How new customer ids are generated in the new and mapping tables could be automated depending on your house rules for how new ones are assigned. You would probably want to create code or SPs to make the process more user-friendly, spit out errors, etc. for duped oldcustids, but at the table level this would be one way to enforce it.

CREATE TABLE [dbo].[Tbloldcustids](
CustID [varchar](255) NOT NULL
PRIMARY KEY (CustID)
)
CREATE TABLE [dbo].[Tblnewcustids](
CustID [varchar](255) NOT NULL
PRIMARY KEY (CustID)
)
CREATE TABLE [dbo].[TblMapping](
[CurCustID] [varchar](255) NOT NULL,
[NewCustID] [varchar](255) NOT NULL
PRIMARY KEY (CurCustID,NewCustID)
)

这篇关于如何设计映射表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！