4

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:

enter image description here

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:

LineItemMasterValue table

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?

  • you said "This would allow me to query the items correctly for that person" - but the other model allows that too, right? – filiprem Apr 27 '18 at 0:20
1

This is largely opinion based as I'm sure a lot of design questions are. As you stated, it can be made to work in both scenarios.

From a growth perspective, I definitely agree with your choice to supply the values in a vertical table with identifiers for each value type. It's a more normalized approach and won't require much dev work if/when more fields are added later. That's really the main benefit. As business needs change(they always do), how much work is it going to be to change or add. A normalized approach is almost always better for changing business requirements.

The other option is simple to be sure. Writing reports against data like that would be very simple. That's really the only advantage that comes to mind. Unfortunately it's not a good one. This is most definitely an OLTP application we are talking about. So we should be focused on user input, not reporting. As long as the data makes sense, reporting shouldn't be that hard anyways.

One other point. We have an application onsite that was designed by a business user with no actual developer input.
It was designed as a single table with no primary key and about 100 columns. Everything is stored on that one table. Normalization WHO CARES!!! Now that the business is tired of the shortcomings of the old system and wants something better, there is almost nothing we can do. The old data can not be directly translated to the new system because the requirements are sooooo different. All of that data has become almost entirely useless.

Anyways, the point of my story is, don't take these decisions lightly. They tend to come back and bite you.

To add a bit of ammo to your point, try asking the business the following questions, Will we ever want to record who filled in values or when they were updated? Will we ever want to record multiple users answers for each field? If the answer of either of these is yes, even if it's a remote possibility, the normalized approach is better.

  • Wow i thought i deleted my question because I ended up using my first proposal the one I came up with. Thank goodness... – JonH Apr 27 '18 at 0:14
-1

I would recommend this:

  • If the set of fields is even remotely static, use columns, not EAV. EAV is bad.

  • create three roles & give them column-level grants, as suggested in your question.

  • Use three perspectives (views) to allow selective updates to Price, MTBF, Tag respectively.

Now let me discuss your opinions:

In this case line item master 1 has three records each for price, mtbf, tag. Pretty straight forward.

The other model (one row with many null-able columns) is also pretty straightforward.

This would also ensure that logging, such as who modified the record, is correct at the record level

The same is true with model #2.

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.

Passing parameters from application to database is nothing bad, is it?

Not to mention the UX would become much more complicated.

Why?

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.

Use metadata-driven form design.

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. (...)

That's right. This issue would be solved by audit trail, which you probably need anyway.

So I know both solutions could work...I just keep favoring proposal one just because of my database experience.

Funny - I have DB experience too, and I favor 2nd approach.

We could make it work with proposal two but I continue to feel it is a hack.

No it's not. Unless your field set is very dynamic (no single authority, people can invent fields on the fly).

  • It's a totally valid approach and I've seen it done that way but the question still remains, is there a possibility for expanded business requirements – Shooter McGavin Apr 27 '18 at 0:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.