如何與表中可變但有序的列數建立關係？

首先，我不是 DBA，甚至不是 SQL 人員，所以如果這是一個太多的入門級問題，我深表歉意！我的任務是重做一個舊的 csv 文件並將其轉換為我們擁有的舊 Microsoft SQL Server 2008 上的關係數據庫。它需要跟踪兩種標籤類型（應指定標籤上有多少個序列號以及什麼類型）以及跟踪包括跟踪序列號在內的標籤實例。很簡單，除了棘手的部分是我們的列印軟體需要按正確順序排列的序列號——例如，我們無法列印 MAC 地址所在的保修號。我已將序列號與它們自己的類型表相關聯。但是，每個標籤可以包含任意數量的關聯標籤。

我考慮了兩種方法。第一個類似於舊方法的方式 - 創建 10 個欄位並浪費大量空間，但它不應該太脆弱 - 我們數據庫中最大的標籤包含 6 個序列號，所以這是一個合理的方法。但它感覺脆弱和錯誤，我真的不想這樣做。

另一種方法我有點粗略，這就是我在這裡要問的：似乎我應該能夠建立一系列關係，這會讓我說，“這種類型的標籤在一個中有 4 個欄位， b、c、d 順序，而此標籤在 b、a、e 順序中有 3 個欄位”但我似乎無法弄清楚，我不確定如何開始找到答案。請注意，順序可以在兩種標籤類型之間更改。我想我可以做一些事情，比如為每個標籤欄位分配一個數字優先級，然後按它排序，但這也感覺有點脆弱。

我對一對多關係感到滿意，並且在我正在製定的模式中已經有幾個關係作為我的解決方案。我對多對多關係不太滿意，但我在基本層面上理解了這個概念。我認為我最關心的是鎖定類型定義、實例本身和最終輸出之間的順序。

注意：我沒有直接編寫以下 SQL - 我使用工具繪製了一個模式並將其翻譯成這個（為了清楚起見，我希望）。

CREATE TABLE [products] (
 [id] int PRIMARY KEY,
 [name] nvarchar(255)
)
GO

CREATE TABLE [labelTypes] (
 [id] int PRIMARY KEY IDENTITY(1, 1),
 [product_id] int,
 [template] nvarchar(255)
)
GO

CREATE TABLE [serialNumberInstances] (
 [id] int PRIMARY KEY IDENTITY(1, 1),
 [type_id] int,
 [data] nvarchar(255)
)
GO

CREATE TABLE [serialNumberTypes] (
 [id] int PRIMARY KEY IDENTITY(1, 1),
 [format] nvarchar(255),
 [typeName] nvarchar(255)
)
GO

CREATE TABLE [labelFieldInstances] (
 [id] int PRIMARY KEY IDENTITY(1, 1),
 [labelType_id] int,
 [precedence] int
)
GO

CREATE TABLE [labelSerialInstances] (
 [id] int PRIMARY KEY IDENTITY(1, 1),
 [labelInstance_id] int,
 [serialNumberInstance_id] int
)
GO

CREATE TABLE [LabelSerialTypes] (
 [id] int PRIMARY KEY IDENTITY(1, 1),
 [serialNumberType_id] int,
 [labelType_id] int,
 [labelSerialInstance_id] int
)
GO

CREATE TABLE [labelInstances] (
 [id] int PRIMARY KEY IDENTITY(1, 1),
 [dateCreated] timestamp DEFAULT (now()),
 [type_id] int
)
GO

ALTER TABLE [labelTypes] ADD FOREIGN KEY ([product_id]) REFERENCES [products] ([id])
GO

ALTER TABLE [serialNumberInstances] ADD FOREIGN KEY ([type_id]) REFERENCES [serialNumberTypes] ([id])
GO

ALTER TABLE [labelFieldInstances] ADD FOREIGN KEY ([labelType_id]) REFERENCES [labelTypes] ([id])
GO

ALTER TABLE [labelSerialInstances] ADD FOREIGN KEY ([labelInstance_id]) REFERENCES [labelInstances] ([id])
GO

ALTER TABLE [labelSerialInstances] ADD FOREIGN KEY ([serialNumberInstance_id]) REFERENCES [serialNumberInstances] ([id])
GO

ALTER TABLE [LabelSerialTypes] ADD FOREIGN KEY ([serialNumberType_id]) REFERENCES [serialNumberTypes] ([id])
GO

ALTER TABLE [LabelSerialTypes] ADD FOREIGN KEY ([labelType_id]) REFERENCES [labelTypes] ([id])
GO

ALTER TABLE [LabelSerialTypes] ADD FOREIGN KEY ([labelSerialInstance_id]) REFERENCES [labelSerialInstances] ([id])
GO

ALTER TABLE [labelInstances] ADD FOREIGN KEY ([type_id]) REFERENCES [labelTypes] ([id])
GO

這裡有人會幫助我嗎？

謝謝！

編輯：以下是數據庫需要處理的幾個範例：

#Each non-header row corresponds to the contents of a label
#This product has 2 MAC addresses and 1 kind of SN
PrdNm-----  MM/DD/YYYY  dATM_SN*    Client1MacId1       Client2MacId2
Product1    11/09/2009  00001234    *********6E2        *********6E3
Product1    11/09/2009  00001235    *********6E4        *********6E5
Product1    11/09/2009  00001236    *********6E6        *********6E7

#This product has no mac addresses, but needs a warranty and a different kind of SN

PrdNm---    YYYY/MM/DD  A-B Wty*    A-B  ASA
Product2    2009/07/21  AA1BB2AZ    A1234561
Product2    2009/07/21  AA1BB2AA    A1234562
Product2    2009/07/21  AA1BB2AB    A1234563

關於數據的一些事情 - 有很多不同的列都是 8、10 或 12 個字元串，不一定是唯一的。我認為我可以簡單地將所有這些不同的序列號/mac 地址/保修號合併到一個表中，並帶有指向它們類型的格式化程序的指針。之後，我會在另一個表中定義每個產品，然後最後有一個表，其中每個產品與 N 種序列號相關聯。這部分是有道理的，希望已經反映在我發布的架構中。對我來說沒有點擊的部分是標籤。標籤實際上只是一個表中的時間戳和 ID，然後另一個表將使用所有適當的序列號以及用於排序的整數指向該 ID，以便所有欄位都列印在正確的位置。編輯：我看到這個解決方案的唯一問題是我'

簡單的方法

這忽略Products了可以具有多個值的元素的不同類型和適當的規範化。如果您確實進行了規範化，則需要進行調整以使事情為此發揮作用。

為此，請創建三個實體LabelTemplate、LabelTemplateLine和ProductColumn。

ProductColumn只是Product可以列印在標籤上的可用列的列表。

LabelTemplate將與一個Product.

LabelTemplateLine將確定應列印哪些列以及以什麼順序列印。

CREATE TABLE LabelTemplate
(
 LabelTemplateCd  CHAR(4)       NOT NULL  --Make this a human readable value, feel free to use a longer length
,[Name]           VARCHAR(50)   NOT NULL
,[Description]    VARCHAR(500)  NULL
,CONSTRAINT PK_LabelTemplate PRIMARY KEY (LabelTemplateCd)
,CONSTRAINT AK_LabelTemplate UNIQUE ([Name])
,CONSTRAINT CK_LabelTemplate_TemplateCd_Name_Not_Blank CHECK(LEN(LabelTemplateCd) > 0 AND LEN([Name]) > 0)
)
GO

CREATE TABLE ProductColumn
(
 ColumnName  NVARCHAR(128)  NOT NULL --This is the native SQL Server datatype for column names, you can shorten and/or change to VARCHAR if appropriate
,CONSTRAINT PK_ProductColumn PRIMARY KEY (ColumnName)
,CONSTRAINT CK_ProductColumn_ColumnName_Not_Blank CHECK (LEN(ColumnName) > 0)
)
GO

CREATE TABLE LabelTemplateLine
(
 LabelTemplateCd   CHAR(4)        NOT NULL
,LineNo            TINYINT        NOT NULL  --I'm assuming you don't need more than 255 lines
,LinePrefix        VARCHAR(50)    NOT NULL  --The text that comes before the vale printed
,ColumnName        NVARCHAR(128)  NOT NULL
,CONSTRAINT FK_Line_Of_LabelTemplate FOREIGN KEY (LabelTemplateCd) REFERENCES LabelTemplate (LabelTemplateCd)
,CONSTRAINT FK_LabelTemplateLine_For_ProductColumn FOREIGN KEY (ColumnName) REFERENCES ProductColumn (ColumnName)
,CONSTRAINT PK_LabelTemplateLine PRIMARY KEY (LabelTemplateCd,LineNo)
,CONSTRAINT AK_LabelTemplateLine UNIQUE (LabelTemplateCd,ColumnName)
)
GO

圖片通常更容易理解：

代替實體Label創建一個允許輕鬆查詢所需資訊的視圖：

CREATE VIEW v_ProductLabel AS
SELECT
 Product.ProductId
,TemplateLine.LineNo
,TemplateLine.LinePrefix
,CASE
   WHEN TemplateLine.ColumnName = 'SerialNo' THEN Product.SerialNo
   WHEN TemplateLine.ColumnName = 'VersionDt' THEN FORMAT(Product.VersionDt,'MM/dd/yyyy')
   --etc, etc,
 END AS LineValue
FROM
 Product Product
INNER JOIN
 LabelTemplateLine TemplateLine
   ON TemplateLine.LabelTemplateCd = Product.LabelTemplateCd
GO

所以在這一點上你可能會注意到一些缺點，即你在CASE任何時候添加一個新元素時都需要在語句中添加程式碼，並且你必須做很多手動檢查以確保LabelTemplate它適合給定的類型的Product。

當我有更多時間時，我會為這個答案添加一個更強大的解決方案。

引用自：https://dba.stackexchange.com/questions/273535