Bad Database Design Spotlight

This forum is for programmers who have questions about the source code.
Post Reply
ajhalls
Posts: 36
Joined: Fri Jan 10, 2014 1:41 pm
Location: Utah
Contact:

Bad Database Design Spotlight

Post by 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

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Bad Database Design Spotlight

Post by 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

ajhalls
Posts: 36
Joined: Fri Jan 10, 2014 1:41 pm
Location: Utah
Contact:

Re: Bad Database Design Spotlight

Post by 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

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Bad Database Design Spotlight

Post by 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

ajhalls
Posts: 36
Joined: Fri Jan 10, 2014 1:41 pm
Location: Utah
Contact:

Re: Bad Database Design Spotlight

Post by 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

Thesorrowthefury
Posts: 4
Joined: Thu Apr 18, 2019 9:53 am

Re: Bad Database Design Spotlight

Post by Thesorrowthefury » Thu Mar 25, 2021 12:49 pm

Hi all!

Just seeing if any movement on this has been. I would love to see OD have a 64-bit version on at least 5.6 MySQL.

joes
Posts: 239
Joined: Tue Aug 13, 2019 12:41 pm

Re: Bad Database Design Spotlight

Post by joes » Fri Mar 26, 2021 9:01 am

We are still not actively developing a 64-bit version at this time.
Joe Sullivan
Open Dental Software
http://www.opendental.com

User avatar
Justin Shafer
Posts: 596
Joined: Sat Jul 28, 2007 7:34 pm
Location: Fort Worth, TX.

Re: Bad Database Design Spotlight

Post by Justin Shafer » Mon Mar 29, 2021 8:05 am

You would need to build a 32bit Twain if you went 64 bit... So many scanners lack 64 bit twain drivers...

User avatar
jordansparks
Site Admin
Posts: 5739
Joined: Sun Jun 17, 2007 3:59 pm
Location: Salem, Oregon
Contact:

Re: Bad Database Design Spotlight

Post by jordansparks » Mon Apr 12, 2021 3:25 pm

MySQL could be 64 bit, while OpenDental could be 32 bit.
Jordan Sparks, DMD
http://www.opendental.com

Post Reply