|
I thought normalization was a good idea...
One of our customers need practically all the data in the database to be shown in one WinForm.
He wants to show following:
1. Inventory information (number of items at hand, number reserved, and about 50 other columns)
2. Item characteristics (we can have different characteristics for different items. For example Thickness and Color for polythene, Thickness-Color-Texture for Glass)
3. Order information
4. Some other sh*t
Somehow I have managed to put all this to a WinForm, as a grid. The features of the new form include:
1. About 400 columns per row. (Wish the user luck, may he find it very easy to find information)
2. The database view have 8 tables (Oracle) joined, two of them pivoted.
3. We have about 100 records (inventory items) for testing, and the execution time for the database view is nearly 1 second. I can't wait until this goes in the production environment, where thousands of inventory items are handled.
Apparently normalization was a bad idea.
|
|
|
|
|
Life would be so good without customers!
If performance is an issue, you can have persistent views (If I recall correctly) in Oracle, which essentially will allow you to denormalise the Db in a controlled manner for performance gains.
|
|
|
|
|
Quote: persistent views
Not an option. As order information changes frequently.
Actually I could (and did) propose few technically, and UX-wise sound solutions. But this guy insists he wants it that way. Customer is King!
|
|
|
|
|
Hi Krumia, Yes in many situations the customer is "King," and they want what they want.
But, unfortunately, it seems that, historically, Kings never end-up being blamed when what they insisted be done is a failure. Somebody else must "take the fall."
You did your duty by raising questions, proposing alternatives; I just hope you have a traceable e-mail-and-paper-trail (yes, make printed copies, keep them off-site, save the e-mails to cd or whatever, and keep them off-site) of your suggestions and/or reservations about the customer's demands, so if anyone tries to blame you for a non-performant solution:
You got your ass covered. I have known folks whose jobs included a sign-on bonus, paid after one year on the job, or when they were laid-off, unless: they were fired "for cause."
And, yes, they were fired "for cause," even though they were not at fault for a dramatic product release failure related to a buggy beta being too-soon decided by management it was ready to go to their customers; they got a lawyer, and threatened to sue, which caused the company firing them to quickly re-frame the firing as a lay-off, and to pony up the sign-on bonuses immediately, because:
If the suit had actually been filed, and come to the attention of the Board of Directors (and, legally the CEO, or whatever person was at the top, would have had to inform the Directors immediately of any suit ... at least under U.S. law) some high-level heads would have certainly rolled. And, the company was terrified of "bad publicity" that might affect stock price.
Of course, if you working as a contract programmer for someone directly, or working for a privately owned firm, such a strategy may be a waste of time, depending on what the labor laws are where you are (they can vary from state to state in the U.S.). And, lawyers ain't cheap to use.
best, Bill
~
Confused by having a brain ? This may help: [ ^] !
|
|
|
|
|
Thanks for this. (wish the rating system was not disabled).
I've got my ass covered alright. But it still feels really bad.
|
|
|
|
|
You might be surprised at how often normalization is a bad idea.
With today's storage, processing, and comms speeds, normalization more often than not turns out to be just a waste of time and effort (which is far more expensive than storage, processing, and comms).
It's one of those lovely academic ideas that makes perfect sense until you find that months of work have been spent on implementing something that doesn't deliver a noticeable performance improvement, and will only cost more and more, because it makes everything three times as complicated to do.
Simple object models usually turn out to be the best.
I wanna be a eunuchs developer! Pass me a bread knife!
|
|
|
|
|
Mark Wallace wrote: find that months of work have been spent on capturing data and now we have essentially the same category spelled as elephant, elphant, eliphant, elefant and elephanting
Which could very easily happen in a denormalized database. Denormalization is good for reporting databases, but can turn into the ugliest monster if used in an OLTP environment.
|
|
|
|
|
If you have an "elephant" object, you can't get that kind of error.
Time to implement: three minutes.
* Edited because I typed'"elephant" model', rather than '"elephant" object'. If the CP database were object-model based, rather than normalized, it wouldn't have been a problem.
I wanna be a eunuchs developer! Pass me a bread knife!
|
|
|
|
|
Mark Wallace wrote: normalization more often than not turns out to be just a waste of time and effort
sorry, but I can't let that go.
Normalisation isn't just about performance - it is about such things as making sure you don't have a customer's name held differently on two tables, your total cost of a sale is always the same as the sum of the cost of the items, your customer's age is correct - even when you entered it last year - etc. etc.
Normalizing everything to 3rd normal form and beyond surely can be counter-effective but that's the difference between good database design and academic database design.
If your normalisation is more often than not a waste of time and effort, then you perhaps find it difficult - personally I have no difficulty with designing a normalised database (indeed I find it natural to NOT store the same information multiple times) so, for me, to design a normalised database is not difficult in the least; what is difficult is designing a system that uses a database that is not completely normalised and ensuring it is consistent and well understood by both the current, and future, developers.
|
|
|
|
|
|
An object model takes a tenth of the time to design (in many cases, the required time can be counted in minutes, rather than hours).
The old argument about "customer's name not being stored twice" has never washed.
- Many smaller databases ("smaller" relating to the number of fields, rather than records) would better be stored in a single table, where it would be pretty obvious if there were duplication.
- Unless your head is somewhat unscrewed, it's pretty nigh-on impossible to duplicate a field when setting up an object model (remembering that this takes a tiny fraction of the time required to normalize).
- So what? What's the problem if the customer's name appears in two places? Either remove an instance or link it, so that it's automatically updated. We really don't have to worry about the storage required by a STR(30) column or twelve.
The other things you mention are to do with functionality, not data storage. Normalization's only connection with functionality is that it creates the daisy chains that have to be followed by everything that needs access to the data.
And no, I don't find normalization difficult. What I do find, while doing it, is that I'm constantly thinking "This is wasting my time"; there are better ways of handling the things that normalization is useful for, and most of what normalization does is not required by most databases.
It's yet another of those "Wow! It sounds Perfect!" solutions that's a complete waste of time. The industry is riddled with them.
I wanna be a eunuchs developer! Pass me a bread knife!
|
|
|
|
|
Ok - I'm not picking a fight here, I'm genuinely interested - am I missing something?
Mark Wallace wrote: An object model takes a tenth of the time to design (in many cases, the required time can be counted in minutes, rather than hours).
I disagree - it takes me around the same time to design an object model to designing the DB to store it in. As someone said below, roughly a class would map to a table, a property to a column etc. In the same way I wouldn't have (for example) the Sex of a person stored as a string as a property of a class, i wouldn't do so as a column in a table (assuming that a) the sex is a limited list of n possibilities, and b) that sex is pertinent to my application in some way more than being descriptive.
Mark Wallace wrote: The old argument about "customer's name not being stored twice" has never washed.
washed, cleaned and brighter than white. If you store the customer's name against every order he makes, and you want to send a letter to each customer - how you going to do it?Who do you address it to? do you send a letter to each individual missspelling?
Mark Wallace wrote: Many smaller databases ("smaller" relating to the number of fields, rather than records) would better be stored in a single table,
I genuinely don't understand unless you are talking REALY small - like an address book application - and even then, what about State and Country - do you want people to type these in each time rather than being able to select from a list?
Mark Wallace wrote: it would be pretty obvious if there were duplication.
To whom? In any case what has this to do with normalisation? If each record in an order table stores teh customer's complete details, and I have had 10,000 orders, are you just saying you'll scan the table to look for duplicate surnames rather than having a customer table?
Mark Wallace wrote: Unless your head is somewhat unscrewed, it's pretty nigh-on impossible to duplicate a field when setting up an object model
to paraphrase; it is hard to create a non-normalised object model? Is that what you're saying? surely it is no harder than creating a non normalised database? it's the same thing surely? its a model - one in some OO language and one in SQL or similar.
Mark Wallace wrote: What's the problem if the customer's name appears in two places?
well, if the customer name is in two places and it's guaranteed to be identical then there's no problem per se. but how good is that guarantee? If John Smith has a number of orders and has reached his credit limit, how to I stop all the orders for him? Or are you storing the customer Id AND the name on each order? If that's the case, then you better be sure that if someone changes the name in one order it permeates all other orders correctly!
(And I speak from actual real world experience here, not from some theoretical ideal)
Mark Wallace wrote: Either remove an instance
Why remove an instance? Why actually create a 2nd instance in the first place? I really am not following you?
Mark Wallace wrote: or link it, so that it's automatically updated.
So, like, erm, have a single table with the name and link it to all orders for teh customer? What? like normalisation you mean?
Mark Wallace wrote: We really don't have to worry about the storage required by a STR(30) column or twelve.
Ok, well normalisation is NOTHING to do with storage. that said, with some pissy database you play with, sure storage isn't an issue, but in many, MANY cases it is With millioins of rows in tables, storage (and indexing in partiicular) are real world issues.
Mark Wallace wrote: The other things you mention are to do with functionality, not data storage.
Normalization is all about functionality and not about storage.
Mark Wallace wrote: Normalization's only connection with functionality is that it creates the daisy chains that have to be followed by everything that needs access to the data.
not in the least bit true, but I begin to see where you are coming from.
You want to build an object graph from some data store, and you have to join table a to table b to table c to table d left outer joint to table x,y and z - and that seems too bloody complicated to you so you say, heck it's a small database, let's stick all the data in a single table and off we go? Grab a record and create the object graph from that?
well, if your object graph is as flat as a Shrove Tuesday delight, and as small as the book of catholic contraception techniques, then that will work, but what do you do with hierarchical data? store comma separated strings in a column?
Mark Wallace wrote: I don't find normalization difficult.
You obviously do because you say it takes you hours? As I mentioned (but may not have come across) I never actually perform a normalisation process - I wouldn't dream of creating a database that wasn't essentially normalised. In the same way as, say, you probably wouldn't create a property
string salary {get;set;}
and store "$95,000" in it - because the $ is the currency in the region you're dealing with and the salary is numeric. (incidentally
, that's normalisation right there!)
Mark Wallace wrote: What I do find, while doing it, is that I'm constantly thinking "This is wasting my time"; t
So you are performing a separate normalisatin step? Wy? Why not design the DB properly in the first place? Its the same as designing your Object model (without having seem your object models, Im making some assumptions here!)
Mark Wallace wrote: there are better ways of handling the things that normalization is useful for,
I think you may be entirely missing the point of normalisation; it is not so much 'useful for something' as ' a good way of ensuring you don't stuff it all up'
Mark Wallace wrote: It's yet another of those "Wow! It sounds Perfect!" solutions that's a complete waste of time.
Uh Oh! Honestly this sounds like someone who has had a bad experience because they don't understand, and has written it off without trying harder to comprehend the benefits or otherwise.
Mark Wallace wrote: The industry is riddled with them.
Like GOTO ? Using properties rather than all global fields? inheritance? OO? MVVM...
|
|
|
|
|
_Maxxx_ wrote: store comma separated strings in a column?
I came across this in an old foxpro application apparently due to a limit of 255 columns. They stored record details there, and we had to analyze RBAR to get what the customer needed...one of the more difficult exports I have had to write!
"Go forth into the source" - Neal Morse
|
|
|
|
|
A 'simple object model' should map pretty easily to a properly normalised database. (Class → table, property → column, object property → foreign key lookup column.) If you can design one, you can design the other.
|
|
|
|
|
Yup.
The advantage being that you put the object model together based on pure common sense, rather than follow arcane processes where you're not supposed to think, just do.
I wanna be a eunuchs developer! Pass me a bread knife!
|
|
|
|
|
Normalization IS a good idea, at least in my case. Because all the business logic would have been far more complicated if the data were not normalized. Imagine having to update all customer orders of a particular customer, when a single attribute of that customer changes. (Oh you know this)
But my frustration is, people not letting the professional handle the problem. This is exactly what has happened in my case. Because of this guy thinks that he knows better than the software guy, we have a crappy form in an application where everything else is pretty much elegantly structured.
I feel like a web designer, who ends up adding a purple marquee to his newly created, metro style website, because customer wants it desperately.
|
|
|
|
|
krumia wrote: I feel like a web designer, who ends up adding a purple marquee to his newly created, metro style website, because customer wants it desperately.
Hands up anyone who hasn't bee to that place.
(It shouldn't take long to count zero hands.)
I wanna be a eunuchs developer! Pass me a bread knife!
|
|
|
|
|
Doesn't sound well normalized to me.
|
|
|
|
|
That's one of the other problems with normalization: by slightly misinterpreting the business requirements (read: by not having decent business requirements, because everyone thought you would just automatically know everything that their jobs entail) you can introduce things at a very early stage that will cause huge problems later, and will require even more huge effort to correct.
I wanna be a eunuchs developer! Pass me a bread knife!
|
|
|
|
|
Mark Wallace wrote: by not having decent business requirements, because everyone thought you would just automatically know everything that their jobs entail
But planning isn't agile! We planned once, and that project was a total failure! We'll never plan again!
|
|
|
|
|
But all your planning involved was asking whether to have donuts or cola. Decent requirements would have told you that was donuts AND cola. It's no wonder your team all fell asleep so soon.
|
|
|
|
|
This is what I mean about missing voting...
I wanna be a eunuchs developer! Pass me a bread knife!
|
|
|
|
|
Perhaps you should convince the customer that having 400 columns in one grid is a bad idea and that putting the data in seperate forms will make stuff faster and easier to use... In the end the customer is always right, of course...
It's an OO world.
public class Naerling : Lazy<Person>{
public void DoWork(){ throw new NotImplementedException(); }
}
|
|
|
|
|
krumia wrote: One of our customers need practically all the data in the database to be shown in one WinForm.
I would suspect that someone has confused the meaning of the words 'want' and 'need'.
I would also suppose that an analysis of the actual business process would reveal that it was an uninformed 'want' as well.
|
|
|
|
|