The Platypus Database 2

[lespaul]

Some of you may have seen my threads mentioning the database I use. It's an AS400, that , as I told my husband, was not designed and developed; it was evolved and it's the platypus of databases. Our court has been using the AS400 for about 20 years. There are hundreds of RPG programs and everytime someone had to develop a new application, if there wasn't a table that had the information a new one would be created that had all the needed information. It didn't matter that there were already tables that had 3/4 of the information. So now we have this platypus database that is unnormalized and no plans in the future to do anything about it.

This really bugs me. I was taught the relational database model and always found the logic of normalization came very easily. Of course, the plus side (can always find one!) is that my SQL skills have increased greatly in order to extract the proper information from my platypus.

Is this situation rare or do any of you have to deal with a platypus?

Leslie

 

[palbano]

platypuses abound! And not just in Database Schema!



-pete

 

[sleipnir214]

The problem is never just the database. It's also the legacy code that requires a messed-up schema to operate.

I've had to deal with stuff like this before, most recently from a web designer who "didn't trust relational databases" (his literal words). It's taken me 3 years to slowly get all that idiotically-designed software and databases straightened out.

The only way to deal with the efficiently is preemptively.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[flapeyre]

In a true relational database, only the keys are duplicated among files - there should be no other information that is duplicated. I don't think that most databses are 100% relational; they all have some duplication. I guess the developer worked in the Department of Redundancy Department .


Was this system on a S/36 or S/38? (had to have been one of the two, the AS/400 only goes back to 1988).




"When once you have tasted flight, you will forever walk the Earth with your eyes turned skyward, for here you have been, and there you will always long to return."

--Leonardo da Vinci

 

[lespaul]

I believe it was the S/38.

During the Y2K fiasco, which offered the perfect opportunity to "fix" some of the problems, nobody did anything! All the dates (which are stored as integers since there was no date field in the old days) are/were stored as YYMMDD. The solution? Add a new field to ALL the tables that store the dates with a YYYYMMDD format and update the programs so that as well as entering the 6 digit year, also enter the 8 digit one! So now every date is stored twice!

As for the web-designer who doesn't trust relational databases, that's just CRAZY!!!

I have recently learned that the AS400 does support relationships, but no one here has implemented them. I got lucky with my first project, I designed it from scratch. So all my tables are set up relationally and I enforce the relationships through my code rather than through the database. My most recent project is the one that has thrown me into the depths of the unnormalized data!

I guess it's 'Long live the platypus hunters!'

Les

 

[SQLSister]

Well at least you can consider it a learning expereince. I've often found the best way to learn the right way to do things is to have to suffer with a system designed the wrong way. Makes you much more aware of why things are important.

As to the web guy, this doesn't surprise ma at all. There are valid reasons for denormalizing a database and access speed is one of them. Since web designers tend to be very conccerned about that, they tend to not like fully relational databases. While the dba has to balance the needs for fast data access against the speed of data entry and updates, the web people are generally nmost concerned with having the pages load quickly. Since the bulk of the data entered in a typical commercial website is not done by the web users, the web designer is not at all concerned about whether denormalizing will slow them up.

 

[jrbarnett]

Hi,

I also had a similar situation, where I worked on one system which was 99% normalised, but there were odd stored totals and other idiosyncrasies. This was designed and developed by computing graduates from highly regarded universities.

As well as working on this one, I developed from scratch an entirely different product from scratch. When I showed it to the people who worked on the other system they were in awe - "wow" it really runs this fast.

My degree project was in the performance design of a relational database. I wrote 4 different schemas to store the same data and equivalent queries to pull off the same data, and timed the execution of these select queries. The only indexes on any of the tables were primary keys.

The fully normalised one was a factor of 10 faster than the slowest one.

John

 

[MDXer]

As an ex-web guy speed is a major factor especially when you have to hit load times that are represented in seconds. Now as and Olap guy I still prefer denormalized schemas for speed but I go about it with a much diffrent approach. As a web guy I would just have the data input in a manner in which I could easily and quickly retrieve it. Now I stick to a star schema whenever possible.


"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[flapeyre]

Our web guys understand that you get the data this way, and that's the way it has to be. The underlying database is not going to be altered just for you.

On the date thing, I used to work in a place where the same date was stored in the same file in MMDDYY, YYMMDD, YYYYMMDD, and Julian (I hate Julian dates!!)


"When once you have tasted flight, you will forever walk the Earth with your eyes turned skyward, for here you have been, and there you will always long to return."

--Leonardo da Vinci

 

[sleipnir214]

SQLSister:
MDXer:

I agree completely that there are times when you have to violate strict database normality to enhance real-world performance. Heck, everyone does that.

But I wasn't clear. This guy's database and resultant code were so inefficient that each page required 3 times more database calls than were necessary. He had, for example, tables filled with columns titled "Field01", "Field02", ... right out to the limits of the database server. And, of course, tables just to describe the names of the "FieldXX" tables.

Then there were the data formats. Three different formats for dates. An internal code that was a string of the form "digit.digit" that he decided to store in a floating-point, so all his code had to deal with rounding issues.

It was a nightmare.


Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[lionelhill]

No, No, why do people charge off into floating point without knowing what it does, how it works, or anything??
(Lionel, on the 19.999999th of the 7.0000001th, 2003ish)

 

[sleipnir214]

lionelhill:
It constantly suprises me how few professional programmers actually know that floating-point representation is an approximation.


Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[benlinkknilneb]

I'm working with a platypus myself... and it's a particularly ugly platypus. I've seen DB's designed by children and domesticated animals that were better organized; sometimes you'll find a *very* important field under the name _1 or something stupid like that, besides the issue of information being duplicated. Fortunately, it looks like we're gradually reworking the system (new IT management) to a more useful, less bloated form.

 

[lespaul]

Lucky you! I have a mental representation of a Database schema that could work, but there's NO WAY anybody is going to allow changes to the database. We're stuck with it!

Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[DarenNM]

I'm currently working on migrating data from a booking system. It isn't enough that it is not a relational database, but a lot of the data has been entered as free-text without the use of input-masks, validation, etc. Each record contains all of the data for that booking (client details, product details, costings, administrative details, etc) - there is no concept of shared data (client profiles, product lists, etc.) I'm no longer surprised that products are entered multiple times, with different codes, costs, and so on.

The data is exported from the system as text files. Some of the text is fixed-width, some of it is delimited with carriage-returns. Most of the time it is impossible to work out which user-remarks belong to which products.

I can go one better on the dates - in the data I am working with they are stored as DDMMM. No year! You have to assume that if the date in the booking is before the current date, then it must be for the following year.

Writing the data-migration applications was a lot of fun.


Must think of a witty signature

 

[lespaul]

Ok, at least I have years, even if I can find the dates EVERYWHERE.

Sounds like your platypus is even more poorly designed than mine (I didn't think that was even POSSIBLE!).

Unfortunately, no one will ever fix this data, it will be a platypus forever!

leslie

 

[KenCunningham]

Hey, I think we're bring unfair on the poor old Platypus here, after all it's made a pretty good stab at surviving for several thousand years, if not more, and other Platipii (??) might not agree that it's an unattractive creature. Agree about the core issues though ;-)

 

[lespaul]

I was not trying to malign the platypus! But you have to admit, it doesn't appear that much thought went into its design (like my database!), it's a mammal, but lays eggs, and has a bill!

Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[KenCunningham]

Some would say it's ideally adapted then - a little bit of everything! I've got bills too, but at least the platypus doesn't have to pay for his/hers! Now the Koala, don't get me started on that specialised beast.

 

[sleipnir214]

<sarcasm>
Before we malign the rest of the monotremes (we've so far missed the echidna) and then move on to the marsupials, could I suggest calling this kind of non-engineered, just-grew-in-place database pseudo-schema a &quot;chimera&quot; database?
</sarcasm>

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!