Database Design Approaches

Below steps are generally required at the very high level to complete any database design

1. gather the requirement and document it in FRS , mostly you will get this from systems analyst or you may have to assist the systems analyst to prepare the document.
2. create a conceptual model using use cases and dataflow diagram.
3. create a logical model (entity,attribute,relation ship) from the conceptual model using any modeling software(eg. Erwin)
4. apply the normalization while trading off between (updates and reports)
5. create a physical model ( choose the RDBMS software, data types, indexes)
6. test the model with the requirement and prototype

 

Do you need to design the database from scratch ?

No, not always. below are the few scenarios and the approaches where you might not need to start from the scratch.

1. check whether any system exists.
   • for most of the applications there might be some existing legacy systems in place , if there is any then get the schema of it and reverse engineer it.
   • if the new system has any new enhancements then modify the design , but before modifying the design , document your changes using an mapping table , so that when you move the data from legacy system you can modify the way the data should flow from the old to new as per the changes mentioned in the mapping table.
2. is there any prototype exists for your application
   1. if your project following a prototype model then you can understand the flow of the application through the prototype and can start designing the database , but make sure about the business rules which you need to apply at the backend.

conclusion : before start designing the model, check whether any system exists and the prototype which helps you to understand the flow of the application.

Its always recommended to create the FRS,conceptual,logical and physical design documents ,though something already exists as these documents helps in making further builds and its up to you whether you create these documents at the beginning, in parallel or at the end of your project based on the timelines and other factors.