Before starting the Database model

Question

What you do before starting the Database model diagram? I mean how you form the Requirements, Specifications etc. Use cases is one thing but anything else? Some best practice or a rule of thumb? Being a self learner I want to see how it goes in the hands of professionals?

Answer 1

Make sure you have a complete list of requirements from your client. Do your best to completely understand these requirements, it will really help in your design if you do. If YOU are defining the requirements it may be easier since you will already have an idea of what you need to do. Having a thorough grasp of your goal is the most important part.

If there is an obvious part of your database that will be the most important (an application in an online application system for instance) I will usually start from there and work out one piece at a time.

Personally I like to draw rough pictures (what ever makes sense to you, doesn't have to be an official ERD) of what I think the database will look like and revise it to finer levels of detail.

Answer 2

Don't rely only on written requirements. There is no such thing as a complete list of requirements. Talk to the stakeholders, ask questions and use the results of those interviews to determine what attributes need to be modelled, how they are used and to identify the business keys. Then some data analysis and investigation is usually needed to determine the right data types and other aspects.

It may be possible to get a good first cut of a data model up front but don't worry if you can't. Data modelling generally ought to be an iterative, agile process, done in sensible sized steps as a project evolves (although there are certainly cases like Data Warehouse design where the agile approach may be harder to apply).

Answer 3

Depending on your clientele, it can be a good idea to have two data models and two diagrams. One model and diagram is for data analysis. The other is for database design.

I have had good results by using an ER (Entity-Relationship) model and diagram for data analysis and an RDM (Relational Data Model) model and diagram to reflect database design.

The ER diagram is useful for communicating the requirements discovered so far back to the clients, and making sure they are complete and correct. ER diagrams are easy to understand even if the client has no background in database theory. As others have responded, this is an iterative process, not a once only waterfall.

The RDM model and diagram is useful for reflecting logical database design decisions such as the decision to normalize data or do something else. Its easy to derive an RDM model from an ER model, although you have to throw in some design decisions that are intentionally omitted from the ER diagram.

In turn, its easy to build a table create script from an RDM diagram. You will have to add some physical features like indexes, in order to obtain good performance without tearing your hair out.