Today has been a bit of cross-eyed day working with an eight-year-old data model in MySql.
To start things off, I found a great little utility called dbForge Studio that would help me with diagrams and figuring out relationships and usage and all that jazz.
Then came the startling revelation that there aren’t actually any relationships in the database. Or indexes. And in more cases than I’m comfortable with, no primary keys. Foreign keys are often stored as CHARs.
Well…at least I have a good idea of where the performance problems are…
Of the 146 tables in the data base, only 75 are currently in use. Of those 75, 22 tables are used as categories or types, so they can be munged down into my RefCode structure. Of the remaining 53 tables, about 12 or so seem to be related to inventory and won’t be used when we bring Passport online, and about 5 tables related to user accounts, meaning I really only have about 36 tables to worry about trying to convert to the new structure.
There are 11 tables beyond the 75 that we don’t yet know if they are in use.
The process has been to go through the corporate portal, go through the help desk site, go through the executive view of things, traverse the external web site sign-up process and finally…ask around. No one knows about those 11 tables…do you?