Translating ER Diagram to SQL Tables
Fast recall
Quick revision
Core answer
Ability to systematically convert an ER design into a normalized relational schema with correct foreign key placement
Placing a child identifier on the one side, which cannot represent several child rows
Walk through a complete multi-entity example showing entity-to-table, attribute-to-column, and all three cardinality types translated to SQL
Interview-ready guide
Interview answer
Translating an ER diagram starts by mapping each regular entity to a table, its simple attributes to columns, and its candidate keys to key constraints.
Relationship mapping then follows cardinality and participation. For one-to-many, a common design puts the one-side key on each many-side row. If one Customer has many Orders, customer_id belongs in Order and is NOT NULL only when participation is mandatory.
For many-to-many, create a junction table. Student-Course becomes Enrollment with both foreign keys; their pair can be the primary key or a UNIQUE constraint beside a surrogate key. Relationship data such as enrollment date belongs there.
For one-to-one, a UNIQUE foreign key on the dependent side enforces the maximum of one. Multivalued attributes commonly become child tables, while derived values may be computed instead of stored.
Finally, verify nullability, delete behavior, weak-entity keys, constraints, and normalization against the original diagram.
Translating ER Diagram to SQL Tables
Direct Answer
Map each regular entity to a table and its simple attributes to columns. A 1:N relationship normally places the parent's key on the many side; an M:N relationship becomes a junction table; and a 1:1 relationship commonly uses a UNIQUE foreign key on the dependent side. Then map optional participation, weak entities, multivalued attributes, and relationship attributes with explicit constraints.
Code Example
-- Entity: Customer (strong entity)
CREATE TABLE customer (
customer_id BIGSERIAL PRIMARY KEY, -- Underlined attribute in ER
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE
);
-- Entity: Order (strong entity)
CREATE TABLE "order" (
order_id BIGSERIAL PRIMARY KEY,
order_date DATE DEFAULT CURRENT_DATE,
customer_id BIGINT NOT NULL, -- 1:N relationship, FK on N side
CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);
-- Entity: Product (strong entity)
CREATE TABLE product (
product_id BIGSERIAL PRIMARY KEY,
product_name VARCHAR(100),
price DECIMAL(10,2)
);
-- M:N Relationship: Order contains many Products, Product in many Orders
CREATE TABLE order_item ( -- Junction table for M:N
order_id BIGINT,
product_id BIGINT,
quantity INTEGER NOT NULL, -- Relationship attribute
PRIMARY KEY (order_id, product_id),
CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES "order"(order_id),
CONSTRAINT fk_product FOREIGN KEY (product_id) REFERENCES product(product_id)
);
Common Mistakes & Pitfalls
- FK on wrong side in 1:N: Freshers put an order_id array or comma-separated list in the customer table instead of customer_id in the order table, inverting the relationship and breaking first normal form.
- Translating M:N with single FK: Attempting to implement a many-to-many relationship by adding a course_id column to student table, which only allows one course per student, not many.
- Forgetting primary key constraints: Creating tables without explicit PRIMARY KEY constraints, allowing duplicate or null identifiers that violate entity integrity.
Check your understanding
Answer these without looking back at the article.
- In a 1:N relationship, on which side does the foreign key go?
- How do relationship attributes translate to SQL?
- What is a multivalued attribute and how is it handled in SQL?
Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.