Best way to design database tables with interbase?

Question

I am about to start redoing a company database in a proper fashion. Our current database is a mess and has little to no documentation. I was wondering what people recommend to use when designing an Interbase database? Is there some sort of good visual schema designer that will generate the SQL? Is it better to do it all by hand?

Basically, what are the steps people usually take when designing and documenting a database? If it matters, I intend to use Hibernate as an ORM for the database. (Specific tips with Interbase would be appreciated as well).

thanks!

Answer 1

If this is a brand new database app that has recently been created and is not being relied on in the business, then I say full steam ahead with your re-write / new database. I suspect however that you are dealing with a database that has been around for a few years and is heavily used.

If I am right about the database being a few years old, I strongly recommend against starting from scratch. Almost any production database that has been around a few years will be "messy". This is usually because the real world requirements for programs usually demand the solutions to be somewhat messy. This will be true of your brand new database (should you go this route) a few years from now as well.

Here are some reasons I would not recreate a production database from scratch:

• The live database contains years worth of transactions and customer data that is very valuable. It will be very difficult to transfer this data into a completely different database structure. Believe me, even if the company tells you now they will not need to access this old data, they will.
• Many business rules have probably been built into the database structure, in the form of defaults, triggers, stored procedures, even the data types of the columns, and without examining these very carefully and documenting them, you are likely to leave them out of your new database and spend much time debugging and adding these in when people start using the system and discover the rules are not being applier properly
• You are liable to make mistakes in your new database design, or realise later that the structure needs to change to accommodate a new feature. If you have been making changes to your current database, and learning from that, future changes become easier and more intuitive.

Here is the approach I recommend:

• Understand and document the current database, which will give you a really good understanding of the information flows in your business.
• When you see what appears to be bad or messy design, look at it carefully. You may be right, and see potential for change, or you might find a trade off was made for performance or other reasons, and you can learn from this.
• Make incremental improvements to the database structure, being sure to update documentation, alter the programs that rely on those areas (or work with your programmer if that isn't you).

I know this seems like a very long way around, but take it from someone who has been maintaining and creating databases for 12 years now - your current database is probably messy because the real-world requirements are messy.