Comment by oreglio | Hacker News Reader

Comment by oreglio | Hacker News Reader

Parent Full thread

I worked in a company that have been using a sql database for a really long time, like +20 years.

In the original design of the database, they though it would be a great idea to use negative ID for system use (on the same tables that are being used for user data). No foreign key whatsoever.

Forward 2018 when we have to build a system that have to work with this widedly used database (it's for a french ERP). Because there is no index or foreign keys the queries are really slow which make our interface slow. Because the tables are accessible but belong to the ERP we cannot modify them because the ERP is under a licence so we don't have access to remodeling of the tables or how the data is processed before being saved in the database.

So to work around the problem we had to create duplicates of the tables with proper index and foreign keys and start a cron job every minute to read from the original tables and fill the datas into our tables ...

Mind boggling .. The software is being sold to a lot of companies which is kind of sad.

Not that this dismisses the ridiculousness of the original design, but couldn’t you just use materialized views as a workaround?

Yes, that would make it easier. However, they still would have to be triggered by a cronjob or something like that.

Just curious: was there some problem caused by the negativity of the IDs? Or, was the problem mostly having to do with the lack of indexes? Not sure I understand the problem.

The problem for me was that, they used negative ID to store unrelated data from the table intent. It's like having a kind of "hidden world" within your tables.

There is a lot of topics discussing the use of negative ID when the ID store the same datas all the way from negative to positive.

But storing unrelated datas - data reserved to the system for the developper - on the negative IDs of table ... to "save space" ? That for me is bad database design and a problem.

Top quality software isnt as valuable as we like to think.

Yeah until it comes back and bites you in the a$$. Even toy projects take longer than supposed because we devs think it's a one time thing only. My experience is most of the time software lasts much longer than expected.

Not for customers. What we developers call quality is usually readability and maintainability. But that doesn't affect customers one bit if the software solves the problem it is supposed to.

Of course it affects price and development speed which may in the long run sink the company in the marketplace, like what happend to e.g. QuarkXPress.

Whatever happened to QuarkXPress? I remember seeing it all over the place and it was the de facto standard in desktop publishing for quite awhile in the 90s, but then it just sorta... disappeared. Do you know of anywhere I could find a good technically-aimed history or breakdown of what went wrong?

Not sure - rumor is they outsourced all development to India to save money. But the program became very crash-pone, and they became extremely slow to add new features. And then InDesign came on the market, iterated quickly, and ate Quarks lunch.

Even 20 years ago not having any FK's is problematic I would have never done that as a green developer 35 years ago.

Reply on news.ycombinator.com