I sort of know the answer to this but I want to see if I am over engineering this. Please note I've done many things like this in the past so although not an expert I am pretty good with sql server (not brent ozar good but pretty good).
In any event a client has come up to us with this funky spreadsheet that he wants converted using an application. The system consists of a a list of items where certain columns in the excel file are filled out by different people.
There is a design dilemma where one of the programmers has an idea of how to do it and the other one has a different idea. First let me give some background on what the excel file has which will help you understand the problem:
The above you see some information from column A to F. Columns A-C are not really editable, once they are in the system they are considered good. The person who enters that information does not know the answers to columns D,E,F.
In fact column D only someone in purchasing can answer, column E only our R&M engineer can answer, and column F only our Mechanical engineer can answer. In the old days (excel days) the person who generated this file would simply send this excel file to purchasing they would enter column D and send it back. Then the person who created this would send it to our R&M engineer to enter column E and he too would fill it out and send it back..and so on same thing with the mechanical engineer.
Fast forward today, the person who created this file decided he hates this process (And I can see why) and wants this automated a bit and inside of a new application. So one of the design decisions I made is to have a table that stores columns A, B, and C with a primary key.
LineItemMaster ID Ident Manufacturer Description
I wanted to separate the other columns as actual rows in another table (I'll explain more of this later). So example records for the above table.
LineItemMasterID Ident Manufacturer Description 1 1021 Acme Co 12 mm CAP 2 1022 Acme Co 10 mm CAP 3 1023 Foo Co Socket 4 1024 Foo Co Ball Screw
For simplicity please ignore that the manufacturer is repeating and the fact that the ID could simply be the Ident number to have uniqueness, ignore these issues for now. With this table I would then introduce the various column types that need to be filled in as a FK, as simple as TypeID, Type for a Types table.
TypeID Type 1 Price 2 MTBF 3 Tag
I would then introduce another table that would point the line items from the LineItemMaster with the types and allow for a value entry. I know this can get scary because this is starting to look like a Key Value table and those could get ugly, but allow me to continue, so we'd have this:
ID LineItemMasterID TypeID Value 1 1 1 12.00 2 1 2 NULL 3 1 3 NULL 4 2 1 NULL 5 2 2 100
In this case line item master 1 (which is the 12mm CAP) has three records each for price, mtbf, tag. Pretty straight forward. This would allow me to query the items correctly for that person and have them update their specific values. This would also ensure that logging, such as who modified the record, is correct at the record level, etc.
Now the other option (option 2) is to simply have a table that looks just like our excel file where we have
Ident Manufacturer Description Price MTBF Tag
All in one table and my colleague insists that we would just allow them to update their respective columns only. But in this case our code would have to pass parameters or come up with dynamic sql to only select specific columns from this dataset. Not to mention the UX would become much more complicated. It also suffers from if we grow horizontally and there is a new column added next to F (Tag) this would be a design change.
I pointed out yes that this second method is simpler in terms of its a pretty flat structure vs we would have to transpose row data (the line item master types table) into columns (but hey that's part of the user experience, at least the database is more normalized).
I also pointed out that the ModifiedBy field and ModifiedOn field would have the last person who made their edit on that row in the second proposed solution. Sure we could create a log table and any time a change is made log it in a seperate table. But in this case if we took proposal one the edited by field would be right because it would be stored in the line item master type table of who made the edit.
So I know both solutions could work...I just keep favoring proposal one just because of my database experience. We could make it work with proposal two but I continue to feel it is a hack. I didn't want to give much more details as I am giving a simple case, but imagine this has to reference separate other relationships such as "Tasks". If we went with proposal one we'd have one row that has an ID and I'd link that to the task, but that same task could happen thrice (R&M, Pricing, Tag #). The second solution because there are three separate records for the three different people I could attach each task individually and it would make more sense
What can you guys share or think?