How To Design A Database: Entities, Attributes, Relationships And More

Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive
 

Entities

The type of information saved in a database is separated into entities. Entities can be people, events, locations, and even things. If it doesn’t fit into one of these categories, it is most likely a property of an entity, which makes it an attribute. Entities are objects or concepts that can easily be uniquely identified from other objects or concepts, such as a username, or an email address. Each is owned by a specific person and only that person can have that particular identifier. An entity set is a collection of similar entities, such as staff members or players.

 

Identifying Relationships

A relationship is an association among two or more entities. The relationship must be uniquely identified by the participating entities. A relationship can also have descriptive attributes to record additional information about the relationship.

 

Ternary Relationship Set

A relationship set does not need to be an association of only two entities. It can involve 3 or more, when applicable.

 

Recursive Relationships

Recursive relationships occur when an entity participates more than once in a relationship. For example, let’s use a supervisor and her employees. The supervisor is still an employee of the company.

 

Redundant Relationships

Redundant relationships are unnecessary logic that causes unnecessary data to be stored more than once in the database. Redundancy can cause confusion and congestion. Therefore, deleting redundant information is best practice for designing a database.

 

Let’s look at an example of a redundant relationship. There are relationships between a customer and a product, a customer and a sale, and sales to products. Indirectly, there is a relationship between customers and products through sales. Relationships between the customer and the product are made twice, which makes it redundant. Since products are only purchased through a sale, the relationship customers to products can be deleted.



Many to Many Relationships (M:N)

M:N relationships need to be solved by splitting them into two 1:N ships since M:N are not directly possible in a database. M:N says that a number of records in one table  belong to a number of records from another table. Resolve this issue by creating a new entity that is in between the two related entities.

 

Attributes

The data elements for each entity are attributes. Attributes are used to describe a particular entity. A person’s name, his height, and social security number are all attributes. Other examples include the price, manufacturer or type of a product. Each attribute comes from a specified domain. For example, a person’s name may be allowed to be a 20-character string, a social security number is a 9 digit integer, etc.

 

Entity Relationships Diagram (ERD)

An ERD is a type of structural diagram used in database design. It illustrates a database using different symbols and connectors clearly displaying all the data that is to be stored within the database. An entity set is drawn as a rectangle. Attributes are drawn as ovals. Attributes that belong to the primary key are underlined, etc.

 

Assigning Keys

 

A primary key is one or more attributes that uniquely identify an entity. A key that consists of two or more attributes is called a composite key. A foreign key, sometimes called a referencing key, is a key used to link two tables together. A Foreign Key is a column or a combination of columns whose values match a Primary Key in a different table.

 

You may also like these related articles:

 

Steps for Best Performance when Designing a Database

How to Design a Database: 6 Easy Steps You Won't Want to Miss for Better Performance

 

Improving Queries

How to Improve Oracle SQL Queries Using 7 Best Practices

 

The design process from the very beginning

What you Need to Know Before Designing a Database

 

Is your database healthy?

State of Health Check: How to Make Sure your Database is Healthy and Secure