March 3, 2017 at 5:48 am
Hi there, I just needed to get some collaborative advice. I work on a system that is 15 years old and really isn't normalized at all and needs to be redesigned. We already know the flaws in everything. With that said, we had someone in our user community make a claim that our system was violating first normal form rules because of 1 field that we have. He cut and pasted the first sentence from Wikipedia about each attribute must be unique and is barking because we're "violating" it.
So, the table - it does have a primary key but its not actually "set" as the primary key. Each row is a unique row for every item that we have and then we have a field that holds a description of the item that is a varchar(50) data type. When one of these items is discontinued, the description gets updated to say "discontinued effective 1/1/2011" or something like that. This person is claiming that we are putting a status in a description column and this is therefore violating normalization rules.
My argument is its a freeform text column for a description of an item. That is a description that says its discontinued and pertains to the item. If it were a status indicator it would have an "X" in it which is what we use in our status fields to show something has been deleted. Also, this dude has no way to know how our database is set up...I could be creating a reporting view for all he knows just to display this information to the users.
Anybody have any other comments or suggestions? We would like to make the response very "in your face" and "how dare you" kinda.... but I also want to have all my ducks in a row. Thanks!
March 3, 2017 at 6:13 am
amy26 - Friday, March 3, 2017 5:48 AMHi there, I just needed to get some collaborative advice. I work on a system that is 15 years old and really isn't normalized at all and needs to be redesigned. We already know the flaws in everything. With that said, we had someone in our user community make a claim that our system was violating first normal form rules because of 1 field that we have. He cut and pasted the first sentence from Wikipedia about each attribute must be unique and is barking because we're "violating" it.So, the table - it does have a primary key but its not actually "set" as the primary key. Each row is a unique row for every item that we have and then we have a field that holds a description of the item that is a varchar(50) data type. When one of these items is discontinued, the description gets updated to say "discontinued effective 1/1/2011" or something like that. This person is claiming that we are putting a status in a description column and this is therefore violating normalization rules.
My argument is its a freeform text column for a description of an item. That is a description that says its discontinued and pertains to the item. If it were a status indicator it would have an "X" in it which is what we use in our status fields to show something has been deleted. Also, this dude has no way to know how our database is set up...I could be creating a reporting view for all he knows just to display this information to the users.
Anybody have any other comments or suggestions? We would like to make the response very "in your face" and "how dare you" kinda.... but I also want to have all my ducks in a row. Thanks!
so how would I know what the original description of the item was?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 3, 2017 at 6:32 am
By looking in history or request information....
March 3, 2017 at 6:52 am
I'm not exactly a normalization Nazi, but I think they have a point. You're overloading the behavior of that column. From a normalization standpoint, that's pretty much a violation of the rules. Generally, each column should mean, or do, one thing. Having a column that's a description AND a marker for logical deletes isn't how I would intentionally design a system given a choice.
Sorry.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 3, 2017 at 6:57 am
amy26 - Friday, March 3, 2017 6:32 AMBy looking in history or request information....
it may help if you provide details of your table....(ie DML) also...where is this "history" you mention.
I have fallen foul of this type of approach on many occasions.....the business thought it was all good, until some bright spark started asking for sales details of items for last x years......linking the sales data back to the "item" table caused all sorts of confusion when the current "item" description displayed "DNU discontinued "
If you have a history table that accurately determines for each item its description and start and end dates of changes..then all well and good...if not, then I would not support your argument.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 3, 2017 at 7:17 am
Ugh I knew this would happen... as I said, we know our DB isn't normalized and we know we need to redesign it. I didn't want this to turn into a oh you need to do this and this... normalization is of course best practice and a "guideline" - I'm just trying to come up with an intelligent enough response to make this guy shush and stretch the "guideline" as far as I can. Its of course not how I would design it either...but it is what it is and our client wants us to tell this guy to go pound sand. It has been this way for over 20 years. I agree by a strict rule it would be show "2 different things" but what if someone actually did expect the description of the item to say discontinued on bla bla date?" I mean it is only doing one thing... its giving you a description of the item. That description just happens to be that it was discontinued.
Yes, we do have start and end dates for any changes made to this description in another table. This table is not visible to the users though. This user is just going off what he sees on the front end screen.
March 3, 2017 at 7:34 am
This is all I put in my "draft" so far, trying to be generic yet rebuting:
Thank you for reaching out and please allow me to address your concerns.
Please be assured that our item definition data does not contain any repeating attribute groups
within our tables and that each row is unique per first normal form guidelines.
The description field is a freeform text field and is a descriptor ofthe unique item attribute.
March 3, 2017 at 7:59 am
So the complaints aren't coming from the developers or a design team or something, but from someone looking at a report? Oh, to heck with that then. Yeah, just say they're wrong and move on.
Having said that, I'm still bothered by the described behavior.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 3, 2017 at 8:16 am
1) Yes, doing that does violate normalization, including 1NF. The text "discontinued effective 1/1/2011" has a date embedded in text. That alone is a clear violation of 1NF.
2) It's rather bizarre that you want to continue to do it. Why not simply add a discontinued_date column? You can use a view to reformulate the original table, for those apps that still require that monstrosity.
3)
That description just happens to be that it was discontinued.
Hmm, no, that's not an item "description", that's an item's status, as we've all just discussed.
I also want to have all my ducks in a row.
Sorry, your ducks are scattered all over the place on this one! 🙂
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
March 3, 2017 at 8:26 am
Amy...is this a business system that you have developer control over...or is it a third party vendor with all sorts of stringent clauses?
if its the latter ; then defer to a higher authority...ie the vendor ...and apologise that it's beyond your control.
if its within in your control.....why dont you change it?....appreciate that may be some work,
but at this stage the info you have given us so far
"Also, this dude has no way to know how our database is set up...I could be creating a reporting view for all he knows just to display this information to the users."
...seems to indicate to me, at least, that all you want is to shut the user up and carry on as before ??
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 3, 2017 at 8:30 am
I don't believe we're saying you need to correct everything wrong with the table right now (I suspect that would be a truly big task). But this change seems pretty straightforward and quite reasonable. Why not do this a better way if you can?
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
March 3, 2017 at 8:41 am
Yes, its just a random user complaining not the developers or the designers or anything. Yes, we just want to shut him up and move on. Yes there is a date in it but dates can be text ... lol. There also is not another field in the table for status, so nothing is being duplicated.
This is government and we can't just "change" stuff when we feel like it. 🙂 I'm not disagreeing with anyone that it isn't good... like I said we just want to shut this guy up and make him go away. We can't change anything until we do the redesign and its approved and it gets funded etc etc...
I mean its a business rule that they want the field to be the way it is... so it shouldn't matter if it violates the normalization rule cause it is what they want it to be for the way they designed it long long long ago.
March 3, 2017 at 8:56 am
amy26 - Friday, March 3, 2017 8:41 AMYes, its just a random user complaining not the developers or the designers or anything. Yes, we just want to shut him up and move on. Yes there is a date in it but dates can be text ... lol. There also is not another field in the table for status, so nothing is being duplicated.This is government and we can't just "change" stuff when we feel like it. 🙂 I'm not disagreeing with anyone that it isn't good... like I said we just want to shut this guy up and make him go away. We can't change anything until we do the redesign and its approved and it gets funded etc etc...
I mean its a business rule that they want the field to be the way it is... so it shouldn't matter if it violates the normalization rule cause it is what they want it to be for the way they designed it long long long ago.
It may be best to just go back on focus on the business end of things in that no one can really say without understanding the business. I could have a phone number attribute and there may be repeating area code values in that attribute so does that mean I need a separate table for area codes? And what if it was a database for phone books? Or finding locations of people? I would guess it could be different depending on the business.
I definitely sympathize with the government thing...it's incredibly frustrating as they determined when and what gets fixed and how many hours go into each effort. But you can also use that to your "advantage" when dealing with the customer as well. Statements along the lines of having identified several areas that may need improvements and that those activities will be up to the other powers that be. There often people who look at a screen and have all the answers on how the database and application can be rewritten. Generic, non-committal responses can sometimes appease them. And as bad as it feels, stroke their ego a bit in the process. You can take a shower after work.
Sue
March 3, 2017 at 9:23 am
Thanks Sue, I have edited my reply to be a bit more generic and non specific and try to deflect to what it is exactly they need and perhaps we can provide them a custom report. 🙂
March 3, 2017 at 9:27 am
Tell him he's right, you agree with him and you've submitted this request to the design team. But because it's the government it has to go through an approval & funding process which could take awhile. Here's everything you need to know about persuasion in one sentence:
People will do anything for those who encourage their dreams, justify their failures, allay their fears, confirm their suspicions and help them throw rocks at their enemies.
So make someone else the enemy, confirm he's suspicions about it and then throw some rocks with the guy. He'll think you're his new hero.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply