Reply to: Crappiest database design in a commercial system

[mudskipper]

I am not a date warehousing chappie but I am taking a guess that seperate table for years data is a way of warehousing ? If so, what is the issue with that ?

Don't think so.

Great fun when users want to shift their year end. Kerrrrching.

[fullyautomatix]

I nominate IBM's cognos

Main reasons:

A separate table for each year's data.
Year/Period/Day stored in date format.

I'm ignoring the completely meaningless table/column names, as presumably they mean something in Swedish, but they certainly don't aid understanding.

I daresay there are other horrors that I have yet to encounter.

Last time I saw something this bad, it had been knocked up in access by a non-IT person.


Anyone come across worse? (I'm scared to read the answers to this question...)

I am not a date warehousing chappie but I am taking a guess that seperate table for years data is a way of warehousing ? If so, what is the issue with that ?

[lilelvis2000]

It's just come to me now. Searching for Cognos Powerhouse brought me a short history

Ah yes Powerhouse. I remember it well. The timesheet entry system at Cognos was written in Powerhouse as well as the bug tracking and customer support systems.

I actually worked on and Windows dev tool (named Axiant at the time) which had the Powerhouse screen designer for the terminal. What a mess that was!

[vetran]

Do tell

they have a concept of facts (transactions) and dimensions (descriptions of data) but not sure if its just our install(s) but they seem to mix up the two so changing a product description or detail means you have to reload invoices. The dates are stored in text. They then interlink facts so you can't get all the data out if you link say a quote to its notes.

The documents to describe the load format are laughable. I think a 3 year old could do a better description.

[darmstadt]

Anything like this: Tablemania! - The Daily WTF

[mudskipper]

you seen OBI(EE)??

Do tell

[Sysman]

Actually, as far as I'm aware and whenever I've come across it, Cognos doesn't have it's own database, per se, but uses a 3rd party database solution such as Oracle or DB2 or MS.SQL Server so it could be just the fact that the person who implemented and designed the application did it this way

It's just come to me now. Searching for Cognos Powerhouse brought me a short history

The PowerHouse language represented a considerable achievement. Compared with languages like "Cobol", "Pascal" and "PL/1", "PowerHouse" substantially cut the amount of labour required to produce useful applications on its chosen platforms. It achieved this through the use of a central data-dictionary, a compiled file that extended the attributes of data fields natively available in the DBMS with frequently used programming idioms such as:

* display masks
* help and message strings
* range and pattern checks
* help and information texts.

In order to support the data dictionary PowerHouse was tightly coupled to the underlying database management system on each of the target platforms. In the case of the HP3000 this was the "Image" shallow-network DBMS, and the entire PowerHouse language reflected its origins.

[mudskipper]

Ah, looks like there's a customisable logical schema sitting on top of it. I don't see that part - I'm just playing with the data.

[mudskipper]

Or the reason it's like that is:



Actually, as far as I'm aware and whenever I've come across it, Cognos doesn't have it's own database, per se, but uses a 3rd party database solution such as Oracle or DB2 or MS.SQL Server so it could be just the fact that the person who implemented and designed the application did it this way

Oh, now that is interesting.

I can see a Kerrrching! opportunity here

Actually not sure that's true - client has lots of clients, some which they've taken over from other providers and all have the same crappy structure. I know that different platforms are an option (the application I built for them needs to be made db independent) but all have the same crappy design. Tables xdb00 to xdb99 for the year data - added confusion that some clients have, for example xdb00 for year 2000 data, others have xdb00 for their first year which could be anything!

[darmstadt]

So the original company pre-dates Codd's introduction of the concept of normalisation in 1970? Still, you'd think they could perhaps have caught up a bit by now

Or the reason it's like that is:

IBM has strived to design Cognos as a tool that can be used by users who don’t have the highest level of technical tools and still be effective.

Actually, as far as I'm aware and whenever I've come across it, Cognos doesn't have it's own database, per se, but uses a 3rd party database solution such as Oracle or DB2 or MS.SQL Server so it could be just the fact that the person who implemented and designed the application did it this way

A Cognos report is only as good as its data. How that data is organized will affect performance, accuracy, and ease of authoring. There is no single solution — before you get can your data right, you need to know how it is going to be used.

[Cenobite]

I know it's not a database, but has anyone had the misfortune to work with a VCS called Borland StarTeam? Ghastly.

[eek]

The old JDE field naming convention was from memory

table id (2), field name (3) field type (3)

trying to write a query in a hurry when fixing a warehouse issue was rather painful...

[Sysman]

So the original company pre-dates Codd's introduction of the concept of normalisation in 1970? Still, you'd think they could perhaps have caught up a bit by now

Codd has been coming up in a few places lately:

EDGAR F. ("TED") CODD

For his fundamental and continuing contributions to the theory and practice of database management systems.

...

As part of his service in the RAF, Codd was sent to the United States for aviation training. That experience led to a lifelong love of recreational flying, also to a recognition that the United States had a great deal to offer for someone of a creative bent like himself. As a consequence, he emigrated to the United States soon after graduating in 1948. After a brief period with Macy’s in New York City, working as a sales clerk in the men’s sportswear department, he found a job as a mathematics lecturer at the University of Tennessee in Knoxville, where he taught for six months

[NickFitz]

Probably not really IBM's fault, they inherited this design and would probably have cost a fortune to change/remodel:

So the original company pre-dates Codd's introduction of the concept of normalisation in 1970? Still, you'd think they could perhaps have caught up a bit by now

[Sysman]

Not nearly as bad but a personal accounting application for the Mac platform a few years ago.

It was painfully slow and couldn't keep up with my typing. A peek at the database schema told me why.

There were a gazillion indexes defined and quite a few of them were fields which could only have 2 values.

I learned that such fields weren't good candidates for indexes back when I was using ISAM files a lifetime ago.