Building Relationships with Variable Columns in SQL Server

database-designsql serversql-server-2008

First, I'm not a DBA or even much of an SQL person, so I apologize if this is too much of an entry-level question! I have been tasked with redoing an old csv file and turning it into a relational database on an old Microsoft SQL Server 2008 we have. It needs to track both label types (which should specify how many serial numbers are on a label and what type) as well as tracking the instances of the labels which includes tracking serial numbers. Easy enough except that the tricky part is our printing software will need the serial numbers in the correct order- we can't print a warranty number where a MAC address goes, for instance. I've associated the serial numbers with their own types table. However, each label can contain an arbitrary number of associated labels. Probably fewer than 10- but I also need to know which order because that's critical.

I've considered 2 approaches. The first is similar to the way the old approach did it- make 10 fields and waste a bunch of space but it shouldn't be too fragile- the largest label in our db contains 6 serial numbers, so that's a reasonable approach. But it feels brittle and wrong and I really don't want to do it this way.

The other approach I'm a little sketchy on and that's what I'm asking about here: It seems like I should be able to have a series of relationships which will let me say, "This type of label has 4 fields in a, b, c, d order, while this label has 3 fields in b, a, e order" but I can't seem to figure it out, and I'm not sure how I'd begin finding the answer. Note that order can change between two label types. I'm thinking I could do something like assigning a numeric precedence to each label field and then ordering by that, but that also feels a little brittle.

I'm comfortable with one-to-many relationships and already have several in the schema I'm drawing up as my solution. I'm a little less comfortable with many-to-many relationships but I understand the concept at a basic level. I think my biggest concern is locking down order between both the type definition, the instances themselves, and the final output.

Note: I didn't write the following SQL directly- I drew up a schema using a tool and translated it to this (for clarity, I hope).

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

Would someone here be kind enough to help me out?

Thank you!

Edit:
Here are a couple of examples of what the database will need to handle:

#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

A couple of things about the data- there are a lot of different columns which are all 8, 10, or 12 character strings, not necessarily unique. I think I can simply collapse all of these different serial numbers/ mac addresses/ warranty numbers into a single table with a pointer back to a formatter for the type they are. After that I'd define in another table each product, and then finally have a table where each product is associated with N types of serial numbers. That part makes sense and is hopefully already reflected in the schema I've posted. The part that isn't clicking for me are the labels. A label will really just be a timestamp and an ID in one table, and then another table will point to that ID with all the appropriate serial numbers, along with an integer for ordering so that the fields all print in the right places.
Edit:
The only problem I see with this solution is that I'd be defining the label precedence across several rows instead of having some table where they'd be predefined, which would be safer, but that might not be possible because I need to order the rows and I don't know how I could do that without an ordering integer in each row.

Best Answer

Simple Way

This ignores different types of Products and proper normalization of elements that can have multiple values. If you do normalize, you'll need to pivot to make things work for this.

To do this, create three entities LabelTemplate, LabelTemplateLine, and ProductColumn.

ProductColumn is just a list of available columns from Product that can be printed on a label.

LabelTemplate will be associated with a Product.

LabelTemplateLine will determine which columns should be printed and in what order.

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

Pictures are often easier to understand:

Instead of an entity Label create a view that will allow the information needed to be queried easily:

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

So at this point you can probably notice some drawbacks, namely you'd name to add code to the CASE statement any time you added a new element and you'd have to do a lot of manual checking to ensure the LabelTemplate is appropriate for a given type of Product.

I'll add a more robust solution to this answer when I get more time.

Related Solutions

Fact table foreign keys null

You can leave the FK to some dimension tables as NULL if those dimensions are not known or not applicable. You just have to remember to use outer joins when you do your reporting query.

Alternatively, some people create a "none" and/or an "n/a" dimension record for data mart dimensions and then populate fact table FKs to point at these rather than using NULLs. People who do this like this approach because they have an aversion to outer joins.

People who use NULL FKs in fact tables usually have an aversion to people who have an version to outer joins. ;) (in other words, this is a stylistic issue which can generate religious wars)

I say do whichever you prefer, but pick one approach and stick to it fervently.

Sql-server – Do I need a separate Id column for this “mapping” table

If you have a one-to-many relationship between Producers and Products (in other words, a product can only belong to one producer), than it would make sense to just put a foreign key reference directly in your Products table:

One-To-Many

create table Producer ( id int identity(1, 1) not null primary key clustered, Name varchar(100) not null ) go create table Product ( id int identity(1, 1) not null, Name varchar(100) not null, ProducerId int not null foreign key references Producer(id) ) go

But if there's a chance that this will be a many-to-many relationship, then your best bet would be to use a Join table.

Many-To-Many

create table Producer
(
    id int identity(1, 1) not null primary key clustered,
    Name varchar(100) not null
)
go

create table Product
(
    id int identity(1, 1) not null primary key clustered,
    Name varchar(100) not null
)
go

create table ProductProducer
(
    ProductId int not null foreign key references Product(id),
    ProducerId int not null foreign key references Producer(id)
)
go

-- adding the primary key also ensures uniqueness
alter table ProductProducer
add constraint PK_ProductProducer 
primary key (ProductId, ProducerId)
go

If you decide to go with the Join table, you wouldn't need to have an additional key, as the combination of ProductId/ProducerId would ultimately be unique. You could use them as a composite key, so you wouldn't need that additional Id field in ProductProducer.

Best Answer

Simple Way

Related Solutions

Fact table foreign keys null

Sql-server – Do I need a separate Id column for this “mapping” table

Related Question