2

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.

Improve this question
7
  • 2
    This actually won't be too difficult. Just need to know: can a product have more than one type of label? Commented Aug 10, 2020 at 21:16
  • No, a product has exactly 1 label. Commented Aug 11, 2020 at 0:57
  • 1
    You mention the importance of ordering label lines. That can only be solved by adding a "sequence_nr" column on those lines. The printing software then needs to make sure it does things like SELECT ... FROM ... ORDER BY SEQUENCE_NR Commented Aug 11, 2020 at 7:54
  • 1
    Meaning you cannot depend on any accidental ordering coming from the way the data is loaded. The result of a SELECT statement in SQL is a table (relation) and relations are unordered by definition. The one and only way to guarantee an explicit order is to include an ORDER BY clause when reading. Commented Aug 11, 2020 at 7:55
  • 1
    It would also help if you could provide a little data example. That will make it easier to view and understand the problem. Commented Aug 11, 2020 at 7:57

1 Answer 1

Reset to default
1

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: enter image description here

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.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.