What Is a Database Schema?

A database schema is a way to describe the structure and relationships of information that will be used and held within a database. Some schemas are written in a language that can be interpreted by database management software (DBMS), while others are wholly abstract diagrams. The primary elements of a database schema are entities and tables. An entity is a data model for an object in a database, while a table is a stored sequence of values or other entities. Like a blueprint, a well-designed schema can be used to see exactly how the architecture of a database is constructed.

Database schema are sometimes necessary to explicitly show how datum is stored and how it relates to the rest of the database. An entity is a representation of a collection of data. Like any data structure, it can have a number of fields. Entities do usually have a unique identifying field, though. This is called the primary key.

A primary key is a link to a table. The key must be unique among all other elements in the table. An example would be a customer number for a database that tracks store purchases. Each entity can have only one primary key.

Entities also can have any number of foreign keys. A foreign key is most often the primary key of another entity in another table. In the above example, a foreign key could be an order number for that customer.

In relational databases, primary and foreign keys are used to express the very important concept of one-to-many relationships. In the example given, a customer can have many different orders and order numbers. The reverse is not true, because the order can have only one customer. One-to-many relationships are part of the reason that a database schema is necessary.

Another type of relationship is called many-to-many. This is an instance in which an entity cannot only have many relationships to a single type, but also can be included in many types by another type. An example of this is how any number of customers can order the same product. At the same time, a customer can order any number of products. This many-to-many relationship is common but requires special data modeling to work within the database schema.

While there are some standard conventions that are followed when creating a database schema, there also are many ways to represent the data models. Exactly how each schema object is represented in an abstract design is up to the discretion of the data modeler. In some situations, a DBMS system will be able to accept a database schema that is written in a specific way and actually implement some of the design automatically.