Relational Database Modeling: Scale to the Next Level in Models, Schema, and Design

To start, a database model determines the logical structure of a database and drives the way data will be stored, organized, viewed, represented, and manipulated. The model also determines how the data can be interrelated. Thus, database modeling is the process of choosing a structure and creating both a design and schema that will guide how the actual database is built.

Database modeling should not to be confused with data modeling. Database modeling is a step in database design, whereas data modeling is a process that incorporates analyzing the data and its relations. You can think of database modeling as a system of architecture and data modeling as a series of flowcharts.

Some people use the term “database model” as a synonym for schema. For others, it describes a higher-level concept, which is the definition here.

5 Types of Database Models

There are many types of database models; each has its own way of storing and accessing data. Here are five common database models:

• Flat File: Data are stored in files, but there is no connection between the files.
  
• Hierarchical: This model is similar to a corporate org chart. Each element has a single parent record, but may have multiple child records. An item can only have a relationship with its parent or child record(s).
  
• Network: Similar to the hierarchical model, but allows many-to-many relationships.
  
• Relational: Data are stored in tables (also called revelars) alongside related data points. Relationships between data are created via keys. Because the relational model is the most common type used in databases, we will discuss it in more depth later in this article.
  
• Object-oriented: Data are represented as objects, as defined in object-oriented programming. Unlike the hierarchical, network, and relational models mentioned above, an object-oriented database can’t be represented in visual terms as simple as tables or org charts, but you can read some concise explanations here, here, and here.

As part of the database design process, an organization must create a schema, which is a representation of the structure of a database. The schema depends on the database model chosen and other factors like business rules, which each company or organization will define for itself. For example, an online retailer’s customer database requires a street address, or an employee database must contain each worker’s Social Security number.

There are two parts to a schema: logical and physical.

The physical part of the schema is like an architectural drawing that shows how the data is stored in the database. But it is not a concrete map: It’s very high-level and doesn’t fill in the minute details. In the example below, you can think of a table in the schema as a physical representation of a relation in the database. A relation, in turn, is a set of tuples, aka rows that constitute a selection of data for a single item.

The logical part of the schema defines what data each table will hold, views that users can access, constraints on the data types for each column, and the stored procedures that will be available. Under the relational model, the logical model doesn’t contain query access plans or the implementation and operational details, which are instead handled by the database management system (DBMS).

The logical schema also defines indexes, both primary and secondary. Indexes are mappings of the data (e.g., a column index maps all the data in a specific column) to speed queries, sort of like an extremely large table of contents. The primary index is a map of where data is stored and includes the primary key, and is used by most queries. A secondary index is not required but can be added to speed queries that don’t include the primary key. Unlike the primary index, the secondary index does not contain all data attributes.

There may also be sub-schemas, such as a relation schema (which describes the table, relation name, attributes, and names).

Fundamentals of Database Design

As mentioned above, picking the database model kicks off the database design process. But in order to determine which model will work best, you should know how your database will be used. To select the right database model, ask yourself the following questions:

• What will the database will be used for?
• Who are the users?
• How will it be used?
• How much data will need to be stored?
• How often will the data be accessed?
• How often will the data will be updated?
• What is the hardware and software budget?
• How will the data be maintained?

Then, when you move on to preparing the design, ask the following questions:

• How will data integrity be enforced? Data must be accurate and complete for a database to be truly useful.
• How will referential integrity be enforced (when applicable)? That is, foreign keys must match a primary key; otherwise, the data with the unmatched key won’t be retrievable.
• How will redundancy be reduced? Redundant data increases storage needs and requires updates to be made in more than one location.
• What steps will be taken to minimize restructuring/denormalization? These processes both take time and consume resources, so they should be avoided.

After the analysis is complete, the design will be further affected by requirements and business rules. For example, if you’re building a database for a healthcare client, your design will need to allow only certain staff (e.g. nurses, but not administrative personnel) to view details of a patient's treatment. Or, in a financial institution’s database, all cash transactions over a certain dollar amount need to be flagged and reported to governmental agencies.

The relational database model is currently the most common design. It’s derived from the relational model created by E. F. Codd in the 1960s at IBM. For a longer history of the model, read ”Everything You Need to Know about Relational Databases.”

E. F. Codd established the relational model based on a number of mathematical theories, including informational theory (created by Claude Shannon in 1948), set theory (started in the late 1800s), and relational algebra and relational calculus (originated by Codd himself). One of his biggest insights was that using attribute names instead of relying on the order of data would work better in a model defined by relations. Codd created 12 rules: Key among them is the information principle, which states, “All information in a relational database is represented explicitly at the logical level and in exactly one way: by values in tables.” To speed response time for queries that are expressed in relational algebra, developers can use algebraic laws to convert a query (or part of it) to an equivalent one that will process faster.

The relational model creates a consistent and logical representation of data that is organized in rows and tables, which in turn can be accessed and linked to other rows and tables by sharing a common field (aka the primary and foreign keys). The primary keys must be unique and define each record (this is also known as the primary key constraint). Read more about primary and foreign keys here.

In the relational model, the data type/domain is the building block of the system. All values in the same column must have the same data type and constraints (for example, the value must be positive, or it can’t be NULL), all columns/attributes must have a unique header, all table names must have unique names, and each row must contain unique data based on the categories defined by the columns. An unique set of connected records/rows is called a relationship instance. For example, in a school’s database, the set of records for student Joe Ray from the students table, connected to his class list from the classes table, and to his grades from the grades table, makes a relationship instance.

By entering queries in the client (the front end), users can navigate among the tables/relations on the server (the back end) to create information by combining the data in disparate tables.

Relational Model vs. Hierarchical and Network Models

The relational model was an advancement from the once-common network and hierarchical database models. They differ in a few ways:

• The relational model allowed people to access data via a direct relationship between files, rather than by paths to and from files, so new relations between files could easily be added.
• The relational model opened up more flexible and natural relations between data points, unlike the hierarchical model that only permitted one-to-one and one-to-many relationships (although the network model did allow many-to-many relationships). Read more about the three relationship types here.
• The relational model doesn’t require the database to be reordered when new data is added.
• Complexity is decreased because changes can be made to the schema without affecting the ability to access data.
• Relational databases support ad-hoc queries and reports, whereas the other two don’t.
• Relational databases are slower.
• Relational databases can be more complex to plan.

The advantages of the relational model made it dominant over the the network and hierarchical models (though they are still in use).

Benefits and Challenges of the Relational Model

Databases that use the relational model have advantages over those that use other models. These include simplicity of organization, ease of creation, ease of data retrieval, built-in data integrity, flexibility of queries (including the fact that programmers can write queries that weren’t anticipated by the database designer), and less data redundancy (due to data normalization).

However, o system is perfect. The relational model falls short in scalability, difficulty of setup, and slower searches and queries when compared to other database models.

SQL

The large majority of databases following the relational model use a version of SQL to access and manipulate the data. However, SQL doesn’t fully follow the relational model laid out by Codd in his paper and later codified in his 12 rules. Here are the exceptions:

• SQL allows duplicate rows/records/tuples in a table.
• A column/attribute can be unnamed in SQL.
• Columns/attributes in a table can share the same name.
• In the relational model, columns/attributes can be in any order. In SQL, the order of the columns/attributes is important.
• Null values are handled differently.
• Columnless tables are not acceptable in SQL.
• Updates to views can have different results than if the updates were made in the base table.

SQL is easy to learn, and there are numerous versions (some open source) of the language available — this accounts for part of its popularity. If you’re interested in learning more, see the SQL commands most used by developers and admins.

Types of Relational Operations

Relational databases have a number of operations. They allow users to add, view, edit, delete, and more. These operations can apply to data, rows, columns, tables, and relationships.

Can the Relational Model Be Applied Beyond Databases?

The relational model has found limited use outside databases. Some compiler programs use it to track data types and related functions for the variables the program uses.

Relational database design is the process of taking the structural ideas already mentioned and applying them to a relational database.

To review, a relational database schema sets the foundation and must include descriptions of the tables, columns, data type constraints (what data can be used in each column or table), metadata (which help manage and find the data in the database), indexes (a map of where different data can be found, which helps speed queries), definitions of the keys (both foreign and primary), and their uniqueness. While rows (also known as tuples) are not defined explicitly, their form will emerge based on the other factors.

In addition, relational databases require data and referential integrity, so the design must account for them. Data integrity covers the following:

• Entity integrity: Every table must have its own defined primary key, and that key in every row has to be unique and not be a null value.
• Domain integrity: All columns in tables must be in a domain that is defined. In other words, every value in the column has to meet the constraints set for that column (for example, you can’t have a phone number in a ZIP code column).

Referential integrity is also a part of data integrity, but is important enough to be called out on its own. It means that relationships between tables must be consistent so that the foreign key in one table matches the primary key in a table that references it. Therefore, if any primary key is changed, then all foreign keys it references must be changed to match.

Finally, the design must incorporate query plans, so users can access the data in order to make decisions. Query plans are a set of steps executed to access data in a database when a query is submitted, and then return the results to the requestors.

When it all comes together — tables, columns, constraints, indexes, rows, and keys reinforced by measures assuring data integrity and referential integrity, all covered by query plans — congratulations! You’ve created a relational database design.

Empower your people to go above and beyond with a flexible platform designed to match the needs of your team — and adapt as those needs change. 

The Smartsheet platform makes it easy to plan, capture, manage, and report on work from anywhere, helping your team be more effective and get more done. Report on key metrics and get real-time visibility into work as it happens with roll-up reports, dashboards, and automated workflows built to keep your team connected and informed. 

When teams have clarity into the work getting done, there’s no telling how much more they can accomplish in the same amount of time. Try Smartsheet for free, today.