Dear Lord, I hate ...

dalecosp

... badly-implemented databases!!! :glare:

laserlight

As in badly designed databases, or badly implemented database systems?

dalecosp

Design. Inherited a flat-ish table with 96 columns, most of which are NULL. Many of those which do contain data are of type "string" but the data is basically numeric; no constraints were placed on the input, so I've spent hours doing things like:

update some_table set sales_volume = '1000000' where sales_volume in ('1m', '1M', '1 M', '1 m', '1 mil') ....

NogDog

My favorites(?) are dates/times stored in character-type fields. :glare:

dalecosp

NogDog;11043987 wrote:
My favorites(?) are dates/times stored in character-type fields. :glare:

I guess I hear that too; I tend to prefer UNIX timestamps in INT fields, but the SQL DATETIME stuff is nice, too.

Oh, and 3 guesses as to what system the "inherited" database was created with .... (Your first two may not count!!) 😉

Weedpacket

How about a database with 289 tables, only 30 of which have any key relationships declared? The DBMS doesn't have a boolean type (so no surprise which one it is) so boolean-type values are stored as "Y" (or "y") and "".
Many of the tables are like yours, dalecosp; as special cases arose, extra columns were added for the benefit of the one or two records that needed the extra data, and all the others got NULLs; later on someone realised that "hey, I don't need to create another column, I can just reuse one of these ones that are always null for this kind of thing". Then came the case where a thing needed that column both for its original use and its new one.... Oh, and in one case I found why no key relationship was imposed: the foreign key field could be referencing the primary key of two different tables (depending on whether the ID was < or > 100,000 - guess how I discovered that one).

dalecosp

Weedpacket;11043993 wrote:
guess how I discovered that one).

I don't even wanna try ... I think you made my brain hurt.

I suppose also, that, like mine, they easily had the resources to make more DB's if they wanted to (not a shared-hosting type situation where you only get one DB so you have tables with prefixes for this app, that app, etc.)?

Weedpacket

dalecosp;11044021 wrote:
I suppose also, that, like mine, they easily had the resources to make more DB's if they wanted to?

Oh, no - this was all for one application. I'm not sure, but I think some tables weren't used.

dalecosp

We may have made the case, then, that a poorly-designed rdbMS implementation (cough) is often responsible for a poorly-designed database.... 😉

Weedpacket

Oh, I don't think a richer type system or table inheritance would save this lot. But at least they were consistent: the database, PHP, JavaScript, HTML and CSS were all of the same quality.

sneakyimp

I'd be willing to bet the money paid to the culprits was also substantial and that the cost and poor performance introduced a deep mistrust in the organization that paid for it.