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.

Specialization and Generalization

Specialization and Generalization

Specialization: dividing an entity into multiple entities based on the unique characters between them.

E.g. In below employee table .the employees who are fresher has a  “trainer-name” , but with out any "Project"

name	age	role	trainer	Project
Amit	21	fresher	smith
Bill	30	developer		Prj-A

split this table into two tables based on the unique characteristics

Developers name age Project   Bill 30 Prj-A Fresher name age trainer Amit 21 smith ..

Generalization: creating a super entity through identifying the common attributes between two or more entities.

with the same example as mentioned above :

Developers name age Project   Bill 30 Prj-A Fresher name age trainer Amit 21 smith

create a super entity based on the common properties found in both the tables (name , age)

Employees emp-id name age 1   Bill 30 2 Amit 21 Developer emp-id project 1 Prj-A Fresher emp-id trainer 2 smith

what is the disadvantages of having foreign keys

What is the disadvantage of having more foreign keys

There is no any disadvantage in particular and in fact it supports the referential integrity which is the major advantage.

But internally when there is a foreign key means there is a overhead on the SQL Server system:

Before delete a master record, the engine will look at the existence of the child record in foreign key table internally and to insert or updating a primary key on child record; the engine will look into the master record in the primary key table

Conclusion: so ,it’s always better to create a clustered index on the primary key and a non-clustered index on a foreign key