Let’s not optimize prematurely. I would much rather focus on an elegant/uncomplicated database design now, than worry about performance issues that may or may not come up in the future. That said, modern databases like MySQL are very efficient, and also there will surely be performance benefits from omitting rows where there is no data item (as opposed to a table with many columns, where unused columns still take up space).
I agree we can arrange things so that the “additional info” table is not necessary for some (perhaps most) issue types.
Rod, we are actually experiencing these sorts of performance issues today with our modern mysql database. Storage space is cheap and getting cheaper. I would rather focus on performance now as our clients continue to grow.
Bill, I doubt your database performance issues have anything to do with the subject we are discussing here. Please contact me privately if you’d like assistance with troubleshooting them; I’ve had successes in that area before.
166K issues was gathered during how many years and how many clients an with how many doktor/fascilities? Adding issues for double amount of doctors could create less than double the amount of issues.
But a simple calculation could also show a complete different picture.
For how long was the database filled with issues, for how many clients, by how many doctors. It should be possible to make some kind of guess as to how much space you need in the next ten years for this setting. How many new issues are registered within a certain time-frame
And then the extra tables with information, can this be calculated in a comparable calculation?
Pimm, we’re not so concerned with the amount of space it requires to store this data. Rather the possibility of having to scan millions of rows in an attribute table to display extra columns for every issue line item on every patient’s chart view.
hey,
The only key(index) on the lists table is the ‘id’. Maybe keying(indexing) more than the ‘id’ column, for example, adding a ‘pid’ key/index would decrease your query universe from 166K to only a few. Again, my expertise in performance isn’t to great, since I’m guessing adding more keys/indexes will add to the size and will take away from performance on the mysql update side.
-brady