Bad Database Design Spotlight

This forum is for programmers who have questions about the source code.

Bad Database Design Spotlight

Postby ajhalls » Tue May 21, 2019 6:25 am

I hate bad database design. Today's spotlight is on Eaglesoft.

I was dumping the database to SQLite and noticed that after a few tables, it stopped at one called "daily_patient_productivity_view". It spent hours dumping this one table, so I went home for the day.

Interested to see what it was, turns out that every day it stores a record for every single patient as to if they have had any credits or debits. Every single patient. Every day.

In this case, there were over 14 million records of :
Day Number | Patient | Debits | Credits
421 142 0.00 0.00
421 143 0.00 0.00

Not to be discouraged by a bad design, they also had an extra 14 million records in "daily_referral_productivity_view" AND another 14 million in "daily_service_productivity_view".

Removing only the records that had no data reduced the database from 1500MB to 250MB.

Bad designs like this slow down your server, make backup's take longer and take up more space, make generating reports take longer, and generally just show ignorance of the developer or apathy for the results on the customer.

Open Dental FTW
Dr. Alan Halls DMD
alan@reminderdental.com
ReminderDental.com - A new way to save
ajhalls
 
Posts: 23
Joined: Fri Jan 10, 2014 2:41 pm
Location: Utah

Re: Bad Database Design Spotlight

Postby jsalmon » Tue May 21, 2019 9:08 am

We're not perfect, but we try really hard to keep the "waste" down. On top of that we try and clean up after ourselves when we discover that it would take too much time to rewrite a wasteful system to be more efficient. E.g. we clean up the signalod table after a while because the data is no longer necessary after X amount of time and is heavily used in larger practices.
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com
User avatar
jsalmon
 
Posts: 1486
Joined: Tue Nov 30, 2010 1:33 pm

Re: Bad Database Design Spotlight

Postby ajhalls » Tue May 21, 2019 12:56 pm

I have spent the last year and a half going through the databases of Eaglesoft, Dentrix, Practiceworks, Easy Dental, Softdent and Open Dental and have some major gripes with the design of all of them with the exception of Open Dental.

My only complaint with Open Dental is that by default it installs a 32 bit version of MySQL 5.5 rather than 64 bit 5.6 which for me runs complex reports / queries up to 500 times faster. That isn't bad DB design, but I would love to see it fixed. I have recommended all our users to do that upgrade manually, but it would be awesome if on a new dual core computer with 64GB RAM if the installed DB was able to use all that hardware.

If it isn't feasible, I will be putting together a video tutorial on how to upgrade.
Alan
Dr. Alan Halls DMD
alan@reminderdental.com
ReminderDental.com - A new way to save
ajhalls
 
Posts: 23
Joined: Fri Jan 10, 2014 2:41 pm
Location: Utah

Re: Bad Database Design Spotlight

Postby jsalmon » Tue May 21, 2019 2:21 pm

We have been wanting to release a 64 bit version of Open Dental (and thus would package the 64 bit version of MySQL with it) but have been focusing on other necessities. E.g. making a smooth and semi-automated MySQL upgrade tool so we can get all of our users off of MySQL 5.5.
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com
User avatar
jsalmon
 
Posts: 1486
Joined: Tue Nov 30, 2010 1:33 pm

Re: Bad Database Design Spotlight

Postby ajhalls » Tue May 21, 2019 2:37 pm

Ah crap, then what will I complain about? I mean, I have to kinda appear non-biased :)
Dr. Alan Halls DMD
alan@reminderdental.com
ReminderDental.com - A new way to save
ajhalls
 
Posts: 23
Joined: Fri Jan 10, 2014 2:41 pm
Location: Utah


Return to Developers

Who is online

Users browsing this forum: Google [Bot] and 6 guests

cron