 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / General DB Topics / General DB Topics / February 2005User Defined Fields - HELP PLEASE!
I've got a political (and technical question) I could use a hand with. I developed (and maintain) an inventory system for a large company. It has over 150 regular users, one of which is bucking really hard for 5-10 "User Defined Fields". Read that as free-form text fields. Can anyone here present a strong argument as to why doing this as a bad idea? Management, as usual, doesn't understand that allowing users to put any random data they want in these fields is bad in the long run. It'd be nice if you can point me to reference material where this is spoken against (either online or in currently available publications). I need something concrete to take with me to a meeting this afternoon. Thanks in advance! >I've got a political (and technical question) I could use a hand with. > >I developed (and maintain) an inventory system for a large company. It has >over 150 regular users, one of which is bucking really hard for 5-10 "User >Defined Fields". Read that as free-form text fields. More likely as "free-form text fields with wildly varying semantics". >Can anyone here present a strong argument as to why doing this as a bad >idea? >Management, as usual, doesn't understand that allowing users to put >any random data they want in these fields is bad in the long run. Quite often, users will use such fields to sidestep table or application constraints. Whether the constraints are right is a different issue. For example, the conceptual model might say "Every mechanical drawing has a date on which it was approved, and the earliest possible approval date for a mechanical drawing is '13-Oct-1953'." A user wants to use 05-Jul-1935. You'll probably find today's date in [Date_approved] (because that's a common application-level default), and something like "Actual date approved was 05-Jul-1935" in a free-form text column. This kind of practice renders both columns almost useless. You really need to know *why* the users want to be able to record "anything at all", and apply a software engineering methodology to manage changes. You can download the latest version of the Capability Maturity Model from SEI (www.sei.org, I think. It's a real ball of worms, but you need to be able to say "Here's a list of things this database has to do", and "Here's how your change screws up a, b, c, and d." >It'd be nice if you can point me to reference material where this is spoken >against (either online or in currently available publications). I need >something concrete to take with me to a meeting this afternoon. Might be too late for that. Consider getting really sick and postponing the meeting. <g>  SignatureMike Sherrill Information Management Systems > I've got a political (and technical question) I could use a hand with. > [quoted text clipped - 11 lines] > > Thanks in advance! If there is data which is relevant, and is too rich to be stored in your database "as is", then I am afraid that I would have to support the irritating user. You should come clean and say that this is not supported, but that you are happy to re-design the database should this requirement be sufficiently important. I am sure you understand the best ways to model data, and it is VERY unlikely that this would be user defined fields. I normally solve similar problems with the introduction with an attribute-value table (and some supporting tables) like this: product_information(product_id int, attribute_id int, value varchar) for each product you can then store any number of attribute-value pairs which will hold the extra information. You don't even need to touch your original table. Incidentally, the reason that tacking 5-10 "fields" onto the "side" of a large table holding your products is a bad idea is normalisation. With N extra "fields" and L lines, you would start with NL null values. Google for normalisation to find out more. John > I've got a political (and technical question) I could use a hand with. > [quoted text clipped - 4 lines] > Can anyone here present a strong argument as to why doing this as a bad > idea? It is a bad idea to the same extent that having junk drawers in your kitchen is a bad idea. The system will not be able to assist users in keeping this data organized in a way that is useful to your company. It is more useful if you have a place for everything and everything in its place. However, one junk drawer might be very helpful, provided everyone in the house knows it is a junk drawer and if they can get something useful out of it, that's great, but don't assume that anything in it is worthwhile. Having 5-10 types of items in this drawer is not so bad. In fact, I suspect that there are days when it would really expedite someone's work to have such flexibility. > Management, as usual, doesn't understand that allowing users to put any > random data they want in these fields is bad in the long run. Sorry I can't be more help -- I'm on the side of those who are trying to improve their productivity in getting their job done while you are standing in their way. You might need to loosen up just a tad on this one? Of course there are others who would agree with you, as this opinion seems to crop up with those of certain personality types (Myers-Briggs of ISTJ or others?). Smiles & Best wishes. --dawn > It'd be nice if you can point me to reference material where this is > spoken against (either online or in currently available publications). I > need something concrete to take with me to a meeting this afternoon. > > Thanks in advance! > I've got a political (and technical question) I could use a hand with. Lotsa luck. Management generally wins this debate. They generally come back within a few months and ask why you can't compute the correlation coefficient of the free form data, and graph it over time. Maybe you could tell them that, when they want to search this field, they'll have to buy a license from Google or Yahoo. Again, lotsa luck! > I've got a political (and technical question) I could use a hand with. > [quoted text clipped - 11 lines] > > Thanks in advance! There are many excellent reasons to not do this, but you can't win the argument. You will just look like a naysayer. I've been able to delay this "feature", but I have rarely been able to keep it out completely. Anyway, here are some reasons: The data is essentially unsearchable. This is the same as the data being lost. May as well not have it. Any search that can be done will likely be incomplete and innacurate. The data will not be used by anyone other than those "in the know". This means that some employees will have one set of information, while another set will have other information. And that's only if there is consistency among those "in the know". The data can NEVER be summarized. It cannot be used for analysis of the business in any way at all. Again, it is lost data. Despite this, management will ask you to report on it. The data cannot be transformed. Example- special codes and abbreviations will stay that way. Need to show a customer this data? Hope they can read Martian. No lookup tables possible. No decoding the codes and abbreviations. besides, each user will enter it differently. Then there are spelling errors. The list goes on and on. The data is almost useless in reports. Need to filter on something in one of these fields? Forget it. Sort on it? Ha! Again, the data is lost. The only right way to do this is to find out what the users think they need to record in these fields, and then build a proper place for it. The most common reason users want this "feature" is that they want to work around the business rules. Good luck- you will need it. >> I developed (and maintain) an inventory system for a large company. It has over 150 regular users, one of which is bucking really hard for 5-10 "User Defined Fields". Read that as free-form text fields. << My rtechnical answer would be to see if you can get him to put the text in a document management system, which probably where it belongs. But I will assume this is more politcal than technical, so let's look for sneaky, rotten, underhanded answers. 1) Email the 150 users and tell them to contact this guy with their suggestions. I have them form a committee or a team to investigate the problem. "We trained hard, but it seemed that every time we were beginning to form into teams we would be reorganized. Later in life, I was to learn that we tend to meet any new situation by reorganizing. And, what a wonderful method it can be for creating the illusion of progress while producing confusion, inefficiency and demoralization." Petronius Arbiter, on the Roman Army - 210 B.C. 2) Tell him that he has to fill out a Sarbanes-Oxley form for each field and have it validated by independent auditors. 3) Tell him it will have to be taken out of his budget and that you have to hire expensive outside consultants. 4) Make him write detailed specs, with validation and verification rules. With free-form text, this can be real fun! Do you want it to speak French, for Canadian users? Bulgarian? Then a spec should include computations and summaries on the new columns. And how the fit into the data warehouse. 5) Give him a list of existing projects from people higher up in the organization that he wishes you to cancel to get right onto this one. Send a copy of the memo to the superiors for feedback. 6) "Delay is the deathly form of denial." -- C. N. Parkinson. If you don't understand that, I will expalin it ... later. > 6) "Delay is the deathly form of denial." -- C. N. Parkinson. If you > don't understand that, I will expalin it ... later. Nice quotation but ... <pedantry> I think this should be: "Delay is the deadliest form of denial." </pedantry> I only noticed this because it is a very relevant quotation for me at the moment and I wanted to find out more about it. <details> This is referring to the tactic of simply deferring decisions until the requestor tires of the game and gives up. But it can also be applied to one simply refusing to accept or recognise a situation that demands action. We hear of people being "in denial". It simply means refusing to accept a situation. And any action is preceded by a decision, or ought to be. You can read in Exodus 8:9-10 where Pharaoh used the delay tactic with Moses. Elijah got frustrated with the people of Israel because they would not make up their minds to follow God. 1 Kings 18:21 And Elijah came to all the people and said, How long will you falter between two opinions? If the Lord is God, follow Him; but if Baal, then follow him. </details> John >> I only noticed this because it is a very relevant quotation for me at the moment and I wanted to find out more about it. << C. Northcote Parkinson is the guy who wrote PARKINSON'S LAW and several other books about the nature of bureaucracy. It is a minor classic, and quite funny. It might still be in print, but if not you can easily get a used copy. Another good read is SYSTEMATICS, a small book on why and how systems fail. My favorite is "A fail-safe system fails by failing to fail safely." And of coruse we need to have a wall poster from www.despair.com. >Another good read is SYSTEMATICS, I think that's _Systemantics_. SignatureMike Sherrill Information Management Systems I will take a contrary view. If the user wants user defined columns then perhaps he or she should have them. There are applications on the market that are built to allow the user to define their own columns along with the vendor provided columns. One such product is MAXIMO, which is primarily an equipment maintenance planning and tracking system. The customer can via the product add user columns. The columns can be updated, used in calculations, and reported upon. It is just a matter of proper design to allow and handle extensions to the base design. Now adding user columns does bring with it the potential that the customer will then attempt to use the system in ways it was not designed to handle. That is a drawback and an argument against adding the user columns. But the most important argument against adding the user columns in that your application was not designed to handle user columns to begin with. To properly handle this functionality the application design has to include a dictionary that defines the user columns, have a means of allowing the customer to reference these columns, and to update these columns. That basically means the application needs its own reporting tool and screen generation tool. Or the columns are/were always part of the screen and just have null values until used. (Table driven screen labels). But since your application was not designed from the start with these features adding user definable columns is not practical. However, if what the customer really wants is to add a few additional columns that the original design did not consider then that is another issue rather than really being "user definable" columns. I would give the customer a fair hearing. You would probably want to start by providing him with a list of general rules for the columns that the customer is considering such as the need to relate to the PK of the tables in question, data type, and formatting. PS - My mention of Maximo should not be taken as an endorcement. I strongly dislike several features of the product namely it requires a DBA priviled ID that allows the Maximo Administrator to define Oracle users, add indexes, and the application runs as the object owner. HTH -- Mark D Powell -- > I will take a contrary view. If the user wants user defined columns > then perhaps he or she should have them. [quoted text clipped - 5 lines] > > HTH -- Mark D Powell -- I'll match your bid and raise you one. Let the users choke. Databases should be designed and built by professionals of which even our industry suffers too many fools. End users defining columns almost by definition means a lack of data integrity. It certainly means a lack of proper indexing, and MAXIMO is a horrifying mess generally implemented by large government agencies that know less about software than they do about balancing budgets. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace 'x' with 'u' to respond) > > I will take a contrary view. If the user wants user defined columns > > then perhaps he or she should have them. [quoted text clipped - 16 lines] > implemented by large government agencies that know less about software > than they do about balancing budgets. I feel it is our responsibility, as DBAs, to, at minimum, warn against bad design. Sometimes it's a losing battle, but at least you tried. Daniel, your opinion has been noted. I am tempted to quote your view of the product as outside business consultants have recommended using it for handling MRO material. Unfortunately I think it is too late to do any good. I believe the traditional primary market for the product has been manufacturing companies that used it to track and plan equipment maintenance. GM uses the product fairly extensively. I think the expansion of the product into other areas has been done in the last few years. -- Mark D Powell -- > Daniel, your opinion has been noted. I am tempted to quote your view > of the product as outside business consultants have recommended using [quoted text clipped - 8 lines] > > -- Mark D Powell -- I ran into MAXIMO with Seattle City Light ... Seattle Washington's city-owned Electric Utility. To say I was unimpressed would be an understatement. Though it should not be interpreted as an indication that any competitor is either better or worse as I didn't get a chance to evaluate them. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace 'x' with 'u' to respond) > I will take a contrary view. If the user wants user defined columns > then perhaps he or she should have them. But you have to distinguish between the (IMHO rather unlikely) case where the user *truly wants* them and fully understands the implications, and the (IMHO much more common) "XY problem" where the user has some (unexpressed) need to solve problem X and has jumped to the conclusion that a particular solution Y (in this case, user-defined columns) is the way to do it. Since Parkinson's been brought up in this thread, I'll bring up Lawrence Peter of Peter Principle fame. He emphasized the importance of specifying a solution in terms of the problem it solves rather than the form it takes (writing in the 1970s, he said that your goal should not be to develop the best home movie camera by 2000, it should be to develop the best live-action recorder, predicting correctly that other technologies would supplant home movie cameras). So in a situation like this the job of the DBA, programmer, architect, etc. is to dig in and discover what real need underlies the user request, and only then implement a solution. Or, to put it bluntly, look at the problem in business terms rather than technological ones. It's part of what I like to call the non-geeky aspects of IT (in fact, sometimes a proper analysis reveals that the best solution to the need is a non- technological one!). Merely pointing out the technical problems with Y won't do it, because the user will understand you as telling them that X can't be solved at all, which they will in fact know is incorrect (they're right that X can be solved, just wrong about how to solve it). The result will just be resistance and pressure, and since they're paying the bills, they'll prevail. >> I will take a contrary view. If the user wants user defined columns >> then perhaps he or she should have them. [quoted text clipped - 5 lines] > that a particular solution Y (in this case, user-defined columns) is the > way to do it. That is not at all what I think of when hearing the term "XY problem". smiles. --dawn (an XX) > > I will take a contrary view. If the user wants user defined columns > > then perhaps he or she should have them. [quoted text clipped - 26 lines] > The result will just be resistance and pressure, and since they're paying > the bills, they'll prevail. I agree 1000% It's almost as if I wrote the above. I run into these problems all the time. Users come to me with problems expressed as solutions. I immediately ask them what the business problem is that they are trying to solve. When people ask we what I do, I stopped telling them that I'm a Database Administrator. They don't know what that is anyway. I now tell them I'm a "Data Detective". They don't know what that is either, but the look on their faces is great. More of a "I'm a confused puppy" instead of "you must be a geek". Eric, nice point. -- Mark -- |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.