Re: Varchars for all fields ......

bijuv@my-dejanews.com wrote: > > Hi, We are working on a new Database Model. This model will be implemented > both in Informix and Oracle. Our Project Leader asked me to define all the > feilds as Varchars so that we don't need to worry about Date formats,Decimal > Precisions etc.. But I know that this will demand more work from me. We need > to use date conversion functions, Numeric conversion functions( ofcourse > toChar() Functions) and May need some extra Stored procedures to do the work. > > I would like to have the group's opinion in this matter. In the words of Mr. Spock, This "is like trying to build mnemonic memory circuits with stone knives and ...". Flame on! Why would anyone in his right mind make such a suggestion! Flame off! Oh darn I am now responsible for the first, and probably the only flame of 1998. Anyway, you are opening yourself to all kinds of trouble. How do you compare a "numeric" column from one record to another when one program inserts data with lead zeros another right justified to 9 columns yet another to 12 columns, and still another left justified...... And do not tell me you will set standards! That will last 4 months then some dolt will make a perfectly valid case for special dispensation to store a different format in some column in new table the "will never interact with any other data" and 4 months after that joining that table to some standards conforming table will become the most important project of the year. And that's just the first thing that popped into my head. Try doing aggregations (sum, avg, min, max) on your data! Let me put this simply. Don't do it, don't do it, don't do it. If your boss argues send him to me. If he begs, turn and walk away. If he starts spitting and turning red in the face, quit. It's not worth it and you know whose fault it will be when it all falls apart in a year or so!?!? WHERE DO MANAGERS COME UP WITH IDEAS LIKE THIS. Before I got here the manager who brought RDBMS into the company came up with the brilliant idea to pack 31 security prices into a 128 character field (the first byte cannot be NULL in Informix) skipping slots when the markets were closed and sometimes storing scaled small float, sometimes scaled integer, indicated by another field along with the scaling factor. I cannot tell you how much grief this brainstorm has caused. Yes it is 1.2ms faster to retrieve a month's prices than it would have been if the prices were in 31 floats and 1.6ms faster than using DECIMAL which would not need to be scaled BUT IT'S NOT WORTH IT. Just a few months ago a security was revalued mid month and the new values could not be represented with the existing scaling factor and the old values could not be stored with the new scaling factor without losing precision. Oh yeah the standard says each records scaling factor can be unique, however, the twit who wrote the programming only accesses the scaling factor on the first row read for a particular security so if a requested range of prices spans multiple months and the scaling factor changes we displayed garbage so all rows needed to be updated throughout history when the scaling factor changed until the code could be fixed! Getting a inkling of what you are in for if you knuckle under? Art S. Kagel