Purpose of Relational Database Schema

What Is Relational Database Schema?

A relational database schema is the tables, columns and relationships that link together the components into a database.

The relational model (RM) for database management is an approach to managing data using a structure and language consistent with first-order predicate logic, first described in 1969 by Edgar F. Codd, where all data is represented in terms of tuples, grouped into relations. A database organized in terms of the relational model is a relational database.

The term database design can be used to describe many different parts of the design of an overall database system. Principally, and most correctly, it can be thought of as the logical design of the base data structures used to store the data. In the relational model these are the tables and views. In an object database the entities and relationships map directly to object classes and named relationships. However, the term database design could also be used to apply to the overall process of designing, not just the base data structures, but also the forms and queries used as part of the overall database application within the database management system (DBMS).

Relational Database Schemas

The Purpose of a Schema

A relational database schema helps you to organize and understand the structure of a database. This is particularly useful when designing a new database, modifying an existing database to support more functionality, or building integration between databases.

The process of doing database design generally consists of a number of steps which will be carried out by the database designer. Usually, the designer must:

  • Determine the data to be stored in the database.
  • Determine the relationships between the different data elements.
  • Superimpose a logical structure upon the data on the basis of these relationships.

Within the relational model the final step above can generally be broken down into two further steps, that of determining the grouping of information within the system, generally determining what are the basic objects about which information is being stored, and then determining the relationships between these groups of information, or objects.

Creating the Schema

There are two steps to creating a relational database schema: creating the logical schema and creating the physical schema. The logical schema depicts the structure of the database, showing the tables, columns and relationships with other tables in the database and can be created with modeling tools or spreadsheet and drawing software. The physical schema is created by actually generating the tables, columns and relationships in the relational database management software (RDBMS). Most modeling tools can automate the creation of the physical schema from the logical schema, but it can also be done by manually.

Modifying the Schema

The structure of a relational database will inevitably change over time as data needs change. It’s just as important to document changes to your relational database schema as it was to document the original design, otherwise it becomes increasingly difficult to update or integrate the database. Be sure to save copies of previous configurations, so that changes can be removed if problems occur.

If you want to learn more about SQLdbm modeling tool you can watch this video explaining what are the main features of SQLdbm: