Entity-Relationship Modeling

Entity-relationship modeling identifies entities, their attributes, and the relationships among them before translating those concepts into a database schema. It helps a backend engineer make data structure and integrity requirements explicit before implementation.

An entity is a distinct thing the application needs to store information about, such as `Customer`, `Order`, or `Product`. Attributes describe an entity, such as a customer's email or an order's creation time. In a relational database, entities commonly become tables and attributes commonly become columns.

Relationships describe how entities are associated. For example, a customer can place many orders, while each order belongs to one customer. ER modeling captures this cardinality, such as one-to-one, one-to-many, or many-to-many relationships.

A many-to-many relationship normally requires an associative entity or junction table when mapped to a relational schema. For example, `Order` and `Product` can be connected through `OrderItem`, which can also store relationship-specific data such as quantity and unit price.

A useful ER model separates independent entities from values that merely describe them and makes relationship constraints explicit. During implementation, the model is translated into tables, primary keys, foreign keys, and appropriate constraints.

Key points

• Identify entities and their important attributes before designing tables.
• Model relationships explicitly and determine their cardinality.
• Use primary keys to identify entity instances and foreign keys to represent references between relational tables.
• Represent many-to-many relationships with a junction or associative table.
• Include constraints that reflect required business relationships and data integrity rules.

Interview questions

How would you model a one-to-many relationship between customers and orders?

I would model `Customer` and `Order` as separate entities. A customer can have zero or many orders, while each order belongs to one customer. In a relational database, `Order` would contain a foreign key such as `customer_id` referencing `Customer`'s primary key. The foreign key helps enforce referential integrity.

What is entity-relationship modeling, and why is it useful?

Entity-relationship modeling is a way to describe the data a system needs before implementing the database. It identifies entities, their attributes, and relationships between entities. It also makes relationship cardinality and important constraints explicit. The resulting model can then guide the relational schema.

How do you model a many-to-many relationship in a relational database?

I would introduce a junction table between the two entities. For example, an `OrderItem` table can connect orders and products using `order_id` and `product_id` foreign keys. The junction table can also hold attributes belonging to the relationship, such as quantity or the price recorded for that order. Appropriate keys and constraints should prevent invalid or unintended associations.

What is the difference between an entity and an attribute?

An entity represents a distinct domain object or concept that the system needs to track. An attribute describes a property of an entity, such as an email address belonging to a customer. A useful distinction is whether the concept needs its own identity, relationships, or lifecycle. If it does, it may deserve to be modeled as a separate entity rather than just a column.

What role do primary and foreign keys play in an ER model?

A primary key identifies a particular instance of an entity within a relational table. A foreign key represents a reference from one table to a key in another table. Together, they allow relationships in the ER model to be represented in the relational schema. Foreign-key constraints can also help prevent references to nonexistent rows.