atabase design and the creation of a data model (also referred to as an “entity relationship diagram” or ERD) is a vital yet at times overlooked part of the software or application development cycle. An accurate data model serves as an essential reference point for database administrators (DBAs), coders and other members of the application development team.
he data modelling process enables the team to uncover additional questions to put to the end users. Sound database modelling provides a foundation for creating well-performing applications from the onset. When quality is fused into the project, the overall time taken to complete the project will be reduced, and this in turn reduces project costs.
he cardinal theme behind effective database design and modelling is “measure twice, cut once”. Good database designers will follow keenly the principles of normalization. Normalization is a database design approach which seeks to enforce data integrity in the database structure. It follows the following objectives:
- Minimization of data restructuring
- Enforcement of referential integrity
- Minimization of input and output through the reduction of transaction sizes
- Minimization of data redundancy
he following five concepts are key in effective database design and modelling:
- An entity is a class of real world objects which have common characteristics about which we want to capture information. It is a logical collection of items which are relevant to your database.
- An attribute is a characteristic of an entity or relationship. It can be either descriptive or quantitative.
- A relationship refers to an association of two or more entities. It represents a business rule and is usually expressed as a verb phrase.
- A primary key is an attribute which uniquely identifies each instance of an entity. Technically, the value that is assigned to it should not vary or change over time.
- A foreign key is basically a primary key which exists in a child entity. The concept of managing foreign keys is called “referential integrity.”
any people consider database design and modelling to be nearly identical. Some data modellers prefer to think of their end product as a relational database which reflects the requirements of the data model. Not all data model are relational, though. A few of them are object oriented, but most of them probably are still hierarchical in nature. However, the majority of databases for which we create data models are relational. So even though these two concepts may overlap, their roles and scopes must be considered to avoid causing major problems to users.