Designing a Database
Do you understand the relationships in databases? What is an attribute, entity, or key? How do you use these in designing a database?
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 Sets
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 and 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 who values match a primary key in a different table.
