Bad Database Design Spotlight
Bad Database Design Spotlight
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
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
Re: Bad Database Design Spotlight
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
Jason Salmon
Open Dental Software
http://www.opendental.com
Re: Bad Database Design Spotlight
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
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
Re: Bad Database Design Spotlight
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
Jason Salmon
Open Dental Software
http://www.opendental.com
Re: Bad Database Design Spotlight
Ah crap, then what will I complain about? I mean, I have to kinda appear non-biased 

-
- Posts: 4
- Joined: Thu Apr 18, 2019 9:53 am
Re: Bad Database Design Spotlight
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.
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.
Re: Bad Database Design Spotlight
We are still not actively developing a 64-bit version at this time.
- Justin Shafer
- Posts: 596
- Joined: Sat Jul 28, 2007 7:34 pm
- Location: Fort Worth, TX.
Re: Bad Database Design Spotlight
You would need to build a 32bit Twain if you went 64 bit... So many scanners lack 64 bit twain drivers...
Justin Shafer
Onsite Dental Systems
817-909-4222
justin@onsitedentalsystems.com
http://www.onsitedentalsystems.com
http://iocsnapshot.com
http://justinshafer.blogspot.com
Onsite Dental Systems
817-909-4222
justin@onsitedentalsystems.com
http://www.onsitedentalsystems.com
http://iocsnapshot.com
http://justinshafer.blogspot.com
- jordansparks
- Site Admin
- Posts: 5769
- Joined: Sun Jun 17, 2007 3:59 pm
- Location: Salem, Oregon
- Contact:
Re: Bad Database Design Spotlight
MySQL could be 64 bit, while OpenDental could be 32 bit.
Jordan Sparks, DMD
http://www.opendental.com
http://www.opendental.com