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?
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.