I've recently inherited a number of databases (Access, SQL Server and MySQL 5) all set up in a very poor manner by different groups of people with different ways of working.
The common traits are having no referential integrity whatsoever, no primary/foreign keys on tables and nonsensical field names so I can guess that none of them were designed by database professionals.
As a first step in order to understand how the data are stored, I've decided the most beneficial first task would be to to produce entity relationship diagrams for the tables in each database, then I'll see about setting up primary keys for each table and referential integrity between tables, fitting in any data fixes if necessary.
Sensible fieldnames can come last, because it will involve changing client access code as well.
Questions:
1. Examining the source code for the applications that use this, I have some database access code (part in VBA, part in SQL scripts and stored procedures). Is there a way I can use this information to help generate the table relationships without doing it manually (because of the number of tables, to do it manually would take a long time)?
2. Any more advice or hints on how to tackle this?
I have no database diagramming software available (other than what comes out of the box or is available for free download)? Getting my boss to spend money on this is unlikely, but any recommendations for useful tools would be appreciated.
I've got phpMyAdmin up now connecting to the MySQL server, but that's it.
All the systems involved (clients and servers) are Windows based. I should also say that I have no experience with PHP at all before yesterday, but am willing to get my hands dirty to understand the code.
Its a daunting task, but will hopefully result in me being able to understand the applications (all home grown in a similar vein to the databases, a mix of VB6, Access VBA and PHP) a lot better, and so provide a better support service.
John
The common traits are having no referential integrity whatsoever, no primary/foreign keys on tables and nonsensical field names so I can guess that none of them were designed by database professionals.
As a first step in order to understand how the data are stored, I've decided the most beneficial first task would be to to produce entity relationship diagrams for the tables in each database, then I'll see about setting up primary keys for each table and referential integrity between tables, fitting in any data fixes if necessary.
Sensible fieldnames can come last, because it will involve changing client access code as well.
Questions:
1. Examining the source code for the applications that use this, I have some database access code (part in VBA, part in SQL scripts and stored procedures). Is there a way I can use this information to help generate the table relationships without doing it manually (because of the number of tables, to do it manually would take a long time)?
2. Any more advice or hints on how to tackle this?
I have no database diagramming software available (other than what comes out of the box or is available for free download)? Getting my boss to spend money on this is unlikely, but any recommendations for useful tools would be appreciated.
I've got phpMyAdmin up now connecting to the MySQL server, but that's it.
All the systems involved (clients and servers) are Windows based. I should also say that I have no experience with PHP at all before yesterday, but am willing to get my hands dirty to understand the code.
Its a daunting task, but will hopefully result in me being able to understand the applications (all home grown in a similar vein to the databases, a mix of VB6, Access VBA and PHP) a lot better, and so provide a better support service.
John