Best practices when creating/modeling databases?

Question

I learned at the University some steps to model a database:

1. Model the problem using the Extended Entity-Relationship Model.
2. Extract the functional dependencies
3. Apply some algorithms to normalize the database (3NF or Boyce-Codd)
4. Create the database

I'm studying Computer Science and since I received that course I'm wondering if I always need to do those steps when creating a complex database for an specified problem.

For example, do PHP / .NET / .. programmers always do that? or there are some tools to simplify that process, maybe using another way of represent the problem instead of the EERM?

Answer 1

Modeling a database is like putting the carriage before the horse. I'd say if anything stop at Entity-Relationship modeling. This is generic enough that you can move from there to Object Modeling which is where the meat of your application lies. The database is there for persistence. The most important thing is getting your class model correct, then you can map the database to support it.

Answer 2

I started using Object Role Modelling recently with the NORMA tool

It compresses points 1 to 3 above, and can generate the script/XSD for point 4.

Answer 3

You can use UML instead of ER diagram, if you are familiar with UML. I would suggest Database Modeling and Design: Logical Design book, it has an example how to convert your domain problem into SQL world.

Answer 4

It depends on your skill set. If you are a database administrator then you might follow the steps that you described because in this case you want to reduce the cost of queries and maintain/improve data integrity.

However, if you're a developer you probably want your application to compile/build/run as a priority and abstract your storage mechanism using an Object Relational Model (ORM) in which case you will think of your persistence more in terms of object relations than entity relations. So you would

• Choose your application stack
• Code your object functions and properties
• Perform some action on objects including updating properties
• Save objects to storage