Best Practices for Relational Database Modeling

Introduction

Relational database model refers to a logical data model that stores data in tables (also known as entities). Each table comprises of rows (commonly known as records or tuples) and columns (commonly referred to as attributes). All the tables in a relational database are linked together. The relational database gets based on a collection of mathematical principles drawn mainly from set theory and predicate logic. The mathematical principles were first used in the field of data modeling in the 1960s by Dr. Codd. All the rows in the table have a unique identifier (UID). The relationships between tables in the relational database model get defined using the UID of one table and joining them with the UID of another table. Pointers do not get used. Data redundancy gets reduced through a process known as normalization. The mathematical basis for the relational database model allows for the minimization of data redundancy and verification of data integrity. Data in the relational database model is presented in familiar two-dimensional tables similarly to a spreadsheet. However, unlike a spreadsheet data does not get stored in tabular form and the model also allows combining tables to form the view that is presented as two-dimensional tables. All the data in a relational database is conceptually represented as an orderly arrangement of data into rows and columns referred to as a relation. Al, the values in a relational database, are scalar, that is at all row/column positions in the table there is one and only one value. Also, in the relational database model all the operations get performed on an entire relation and lead into an entire relation, a concept referred to as closure.

Advantages of a Relational Database Model

1)      It provides accuracy of the data because of data integrity that is built into the model at the field level. Relational database eliminates duplication of records and to detect missing basic key values. It also ensures that the relationship between a pair of tables is valid in the relationship, and at the business level, ensures that the data remains accurate concerning the business itself.

2)      In relational database models, all the changes in logical design or physical implementation of database affect the applications developed for it.

3)      In the relational database model, data remains consistent and accurate since the different levels of integrity can be imposed within the database.

4)      Data can get retrieved either from a specific table or any number of related tables within the relational database. The user can view the information in many different ways.

Disadvantage of a relational database model

1.      A relational database model is not suited for all organizations.

2.      The software programs based on a relational database run very slowly, yet in the model, a relational database system is dependent on the machine performance.

3.      In a relational database model, the relationship among tables gets defined inherently. The user has to know the inner data structure so as to be familiar with the physical information of relations.  

Best practices of relational database modeling

It is vital to develop a good relational database model. The use of a poor relational design can lead to inefficient performance and a database that does not meet the requirements of an organization as well as potentially inaccurate data. So as to properly design and develop relational databases, database administrators must have an extensive understanding to sound relational design practices as well as adhere to them. A best practice in information technology refers to an accepted technique for performing an operation. The database administrators must understand both relational theory and the certain implementation of the relational database management systems being utilized to develop the database. Database design requires a sound knowledge of conceptual and logical data modeling strategies. The section below describes the best practices in developing a relational database model.

Conceptual data modeling

A standard database design practice starts with the design of a conceptual model that later gets transformed into a phase known as logical design.  A conceptual data model captures all the meanings as well as the content of information requirements of a company at a high level of abstraction. Conceptual modeling is considered to be a very critical phase in the design of successful database application. One of the most popular high-level conceptual data model is the entity-relationship (ER) model. An ER model is based on the perception of a real world that is made up of a collection of basic objects referred to as entities and relationships among these objects (Brackett, 2012). The ability of a database administrator to create and interpret entity-relationship diagrams is a critical component in designing relational databases. Database design theory and best practices comprise of a proper understanding of procedures and techniques for converting a conceptual model such as entity-relationship, into a logical model.

The basic units described in an ER model are an entity, entity class, type, attribute, unique identifier and relationship type. An entity is a basic object that an ER model represents, which is a real-world thing in the real world with an independent existence and that is distinguishable from other things. Entities include objects with a physical existence such as a car, person, book, house, a customer or an employee, and also objects with a conceptual existence such as a job, a university course, and a company. An attribute refers to an entity that gets represented by a set of attributes (its descriptive properties). Each entity has an attribute. For instance, an employee entity will have a value for all its attributes. The attribute values that indicate each entity become a significant part of the data stored in the database. An entity type describes a collection/set of entities that all have the same attributes such as cars, persons, customers, etc. An entity type gets represented in the ER diagrams as a rectangular box that encloses the entity type name. An example of an entity type in a university is that all courses would be called “course.” Attribute names get enclosed in ovals and get attached to their entity type by straight lines. The entity type describes the schema for a set of entities that have a common structure. An entity set describes the collection of entities of a specific entity type at any particular point in time. A unique identifier of the entity is an atomic or composite attribute whose values are different for all entities in the entity collection. It is crucial to uniquely identify entities in the entity set of the entity type. A relationship type describes the meaningful association among entity types. The degree of a relationship type gets defined as the number of entity types that participate in a relationship type. A relationship type can be said to be a binary or of degree two (that is involves two entity types) or a tertiary or of degree three (that is involve three entity types) (Umanath & Scamell, 2014).  An example of a relationship type is that treatment is the relationship that exists between a doctor and a patient. An ER diagram is used to represent database schema. By transforming the entity-relationship design into a relational database schema, the relational model aids to convert the entity relationship design into a relational database management system.

Logical data modeling

The logical data model at the subsequent level of abstraction is closer to becoming a blueprint. It depends on the kind of database system a designer wants to implement. The logical data model captures the user’s perspective of data within the framework of relational database technology. The logical data model for a relational database system may get known as a relational data model. A relational data model views data as in the form of two-dimensional tables or relations. The components of a relational data model include a table, columns, rows, primary keys and foreign keys.

A table also known as a relation is the primary data modeling concept of relational modeling. A two-dimensional table refers to a collection of columns and rows. The concept of mathematical relation creates the basis for the data structure in the relational data model. A table gets visualized as a two-dimensional table with rows and columns comprising of only atomic values. Features of a relation include

1) A relation is a table that represents data concerning a certain business project,

2) The rows and columns in a table of a relation conform to specific relational rules,

3) A relation comprises a defined set of columns and an arbitrary number of rows, and

4) All rows contain a set of data values.

Columns in relational data modeling indicate a particular attribute of the business object or entity type. All rows in a relational data model hold the values of attributes for a single occurrence of a business object. Simply, each row represents a single instance of the object and each column for that row shows only one piece of data defining a single occurrence of the business object. One of more attributes in a relation gets designated as the primary key for the relation.

Normalization

Normalization refers to the approach used to develop well-organized entities based on the organization’s information requirements by eliminating redundancy, anomalies and inconsistency in the relational database (Hoffer, Venkataraman & Topi, 2016). The process includes the creation of tables and developing relationships between these tables according to rules that are designed with the aim of protecting data and making it more flexible.

Normalization comprises of five normal forms. The most common are the first three normal forms namely First Normal Form (1NF), Second Normal Form (2NF) and Third Normal Form (3NF).

First Normal Form (1NF) – A table is in 1NF if and only of all columns comprise only atomic values that are there are no repeating columns within a row.

Second Normal Form (2NF) – A table is in the 2NF if and only if it belongs in 1NF and every nonkey attribute is fully dependent on the primary key.

Third Normal Form (3NF) – A table is in 3NF if and only if it is in 2NF and no transitive dependencies exist with the relation.

Normalization in relational database modeling must be used as required so as to maximize the system’s performance. Under-normalization of the database model may lead to excessive repetition of data, whereas over-normalization of the database model may lead into excessive joins across many of the tables. The process of normalization in relational database modeling plays a critical role in enhancing the quality of a logical schema through minimizing data duplication, enhancing data stability as well as facilitating streamlined updates and also data deletions. Normalization in a database modeling also assists in eliminating unnecessary coding.

References

Brackett, M. H. (2012). Data Resource Integration: Understanding and Resolving a Disparate Data Resource (Vol. 2). Technics Publications.

Hoffer, J., Venkataraman, R., & Topi, H. (2016). Modern database management. 12th Edition. Hoffer Pearson Education Limited.

Umanath, N. S., & Scamell, R. W. (2014). Data modeling and database design. Nelson Education.

Sherry Roberts is the author of this paper. A senior editor at Melda Research in help writing nursing research paper if you need a similar paper you can place your order for customized papers.