How to design a mapping table?

2019-08-23 07:03发布

问题:

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.

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

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

回答1:

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)
)