101
0/252
Loading content...
Theory and principles find their true value in application. This page presents five complete M:N mapping examples, each demonstrating the full journey from conceptual ER design through physical implementation. These examples span diverse domains and complexity levels:
Each example includes ER analysis, complete DDL, sample data, and essential queries—the artifacts you'd produce in real database design work.
By the end of this page, you will apply the complete M:N mapping workflow to realistic scenarios, translate ER diagrams to production-ready SQL, implement relationship attributes appropriate to each domain, write efficient queries against bridge tables, and recognize patterns transferable to your own projects.
Domain: An e-commerce platform where customers place orders containing multiple products. Each product can appear in many orders; each order contains many products.
ER Relationship:
Product ←(M)— Contains —(N)→ Order
• Relationship attributes: quantity, unit_price_at_purchase
• Cardinality: Each order has at least 1 product; products may have 0+ orders
Key Insight: The price at purchase is a relationship attribute because product prices change over time, but the order must record what the customer actually paid when they ordered.
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859
-- Entity: Product catalogCREATE TABLE Product ( product_id INT PRIMARY KEY AUTO_INCREMENT, sku VARCHAR(50) UNIQUE NOT NULL, name VARCHAR(200) NOT NULL, current_price DECIMAL(10,2) NOT NULL, description TEXT, category VARCHAR(50), is_active BOOLEAN DEFAULT TRUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP); -- Entity: Customer ordersCREATE TABLE CustomerOrder ( order_id INT PRIMARY KEY AUTO_INCREMENT, customer_id INT NOT NULL, order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, shipping_address TEXT NOT NULL, order_status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending', FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)); -- Bridge Table: Order contains Products (M:N with rich attributes)CREATE TABLE OrderItem ( order_id INT NOT NULL, product_id INT NOT NULL, -- Relationship attributes quantity INT NOT NULL DEFAULT 1, unit_price DECIMAL(10,2) NOT NULL, -- Price at time of order discount_percent DECIMAL(5,2) DEFAULT 0.00, line_total DECIMAL(12,2) GENERATED ALWAYS AS ( quantity * unit_price * (1 - discount_percent / 100) ) STORED, -- Line item notes notes VARCHAR(500), PRIMARY KEY (order_id, product_id), FOREIGN KEY (order_id) REFERENCES CustomerOrder(order_id) ON DELETE CASCADE, FOREIGN KEY (product_id) REFERENCES Product(product_id) ON DELETE RESTRICT, -- Can't delete products in orders -- Business constraints CHECK (quantity > 0), CHECK (unit_price >= 0), CHECK (discount_percent >= 0 AND discount_percent <= 100)); -- Index for product-centric analyticsCREATE INDEX idx_orderitem_product ON OrderItem(product_id); -- Index for filtering by discountCREATE INDEX idx_orderitem_discount ON OrderItem(discount_percent) WHERE discount_percent > 0;123456789101112131415161718192021222324252627282930313233343536373839404142434445464748
-- Sample DataINSERT INTO Product (sku, name, current_price, category) VALUES('LAPTOP-001', 'ProBook 15"', 1299.99, 'Electronics'),('MOUSE-003', 'Wireless Mouse', 29.99, 'Electronics'),('DESK-042', 'Standing Desk', 449.00, 'Furniture'); INSERT INTO CustomerOrder (customer_id, shipping_address) VALUES(101, '123 Main St, Anytown, USA'); INSERT INTO OrderItem (order_id, product_id, quantity, unit_price) VALUES(1, 1, 1, 1299.99), -- 1 laptop at current price(1, 2, 2, 29.99), -- 2 mice(1, 3, 1, 399.00); -- 1 desk at discounted price (was 449) -- Query 1: Get order details with product infoSELECT o.order_id, o.order_date, p.name AS product_name, oi.quantity, oi.unit_price, oi.line_totalFROM CustomerOrder oJOIN OrderItem oi ON o.order_id = oi.order_idJOIN Product p ON oi.product_id = p.product_idWHERE o.order_id = 1ORDER BY p.name; -- Query 2: Calculate order totalSELECT order_id, COUNT(*) AS line_items, SUM(line_total) AS order_totalFROM OrderItemWHERE order_id = 1GROUP BY order_id; -- Query 3: Find best-selling productsSELECT p.product_id, p.name, SUM(oi.quantity) AS total_sold, SUM(oi.line_total) AS total_revenueFROM Product pJOIN OrderItem oi ON p.product_id = oi.product_idGROUP BY p.product_id, p.nameORDER BY total_sold DESCLIMIT 10;Storing unit_price in OrderItem (not referencing Product.current_price) is the 'price snapshot' pattern. It preserves historical accuracy: what did the customer pay? Product prices change, but orders reflect the price at purchase time.
Domain: A social platform where users can follow other users. This is a self-referential M:N relationship—the same entity type (User) participates on both sides.
ER Relationship:
User ←(M)— Follows —(N)→ User
• Roles: follower, followed (asymmetric)
• Attributes: followed_at, notifications_enabled
• Cardinality: A user can follow many and be followed by many
Key Insight: Self-referential M:N requires role-based column naming. Without 'follower_id' and 'followed_id', queries become confusing.
1234567891011121314151617181920212223242526272829303132333435363738
-- Entity: Platform usersCREATE TABLE PlatformUser ( user_id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) UNIQUE NOT NULL, display_name VARCHAR(100), email VARCHAR(255) UNIQUE NOT NULL, bio TEXT, is_verified BOOLEAN DEFAULT FALSE, is_private BOOLEAN DEFAULT FALSE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP); -- Bridge Table: Self-referential M:N with role namesCREATE TABLE Follow ( follower_id INT NOT NULL, -- The user who follows followed_id INT NOT NULL, -- The user being followed -- Relationship attributes followed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, notifications_enabled BOOLEAN DEFAULT TRUE, -- You can't follow yourself PRIMARY KEY (follower_id, followed_id), FOREIGN KEY (follower_id) REFERENCES PlatformUser(user_id) ON DELETE CASCADE, -- User deleted = their follows removed FOREIGN KEY (followed_id) REFERENCES PlatformUser(user_id) ON DELETE CASCADE, -- User deleted = followers cleared -- Prevent self-follow CHECK (follower_id != followed_id)); -- Index for "who follows this user" queriesCREATE INDEX idx_follow_followed ON Follow(followed_id); -- Index for recent followsCREATE INDEX idx_follow_time ON Follow(followed_at DESC);12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667
-- Sample DataINSERT INTO PlatformUser (username, display_name, email, is_verified) VALUES('alice', 'Alice Johnson', 'alice@example.com', TRUE),('bob', 'Bob Smith', 'bob@example.com', FALSE),('charlie', 'Charlie Brown', 'charlie@example.com', FALSE),('diana', 'Diana Prince', 'diana@example.com', TRUE); INSERT INTO Follow (follower_id, followed_id) VALUES(2, 1), -- Bob follows Alice(3, 1), -- Charlie follows Alice(4, 1), -- Diana follows Alice(1, 4), -- Alice follows Diana(2, 4); -- Bob follows Diana -- Query 1: Get follower list for a userSELECT pu.username, pu.display_name, pu.is_verified, f.followed_atFROM Follow fJOIN PlatformUser pu ON f.follower_id = pu.user_idWHERE f.followed_id = 1 -- Alice's followersORDER BY f.followed_at DESC; -- Query 2: Get following list for a userSELECT pu.username, pu.display_name, f.notifications_enabledFROM Follow fJOIN PlatformUser pu ON f.followed_id = pu.user_idWHERE f.follower_id = 2 -- Who Bob followsORDER BY f.followed_at DESC; -- Query 3: Follower/Following countsSELECT u.user_id, u.username, (SELECT COUNT(*) FROM Follow WHERE followed_id = u.user_id) AS followers_count, (SELECT COUNT(*) FROM Follow WHERE follower_id = u.user_id) AS following_countFROM PlatformUser u; -- Query 4: Mutual follows (both follow each other)SELECT u1.username AS user_a, u2.username AS user_bFROM Follow f1JOIN Follow f2 ON f1.follower_id = f2.followed_id AND f1.followed_id = f2.follower_idJOIN PlatformUser u1 ON f1.follower_id = u1.user_idJOIN PlatformUser u2 ON f1.followed_id = u2.user_idWHERE f1.follower_id < f1.followed_id; -- Avoid duplicate pairs -- Query 5: Suggested follows (friends of friends not yet followed)SELECT DISTINCT pu.username, COUNT(*) AS mutual_connectionsFROM Follow f1 -- Users I followJOIN Follow f2 ON f1.followed_id = f2.follower_id -- People they followJOIN PlatformUser pu ON f2.followed_id = pu.user_idWHERE f1.follower_id = 1 -- Starting user (Alice) AND f2.followed_id != 1 -- Not myself AND NOT EXISTS ( SELECT 1 FROM Follow WHERE follower_id = 1 AND followed_id = f2.followed_id ) -- I don't already follow themGROUP BY pu.user_id, pu.usernameORDER BY mutual_connections DESCLIMIT 5;Follow relationships are asymmetric: Alice following Bob doesn't imply Bob follows Alice. This is modeled by distinct column roles (follower vs followed). For symmetric relationships like Facebook friendships, you might store each friendship once and check both directions in queries.
Domain: A clinic where doctors prescribe medications to patients. The prescription is inherently ternary—it ties together who prescribed, to whom, and what.
ER Relationship:
Doctor ←— Prescribes —→ Patient
↓
Medication
• This is a 3-way relationship
• Attributes: prescription_date, dosage, duration, refills_allowed
• Constraint: Same patient can receive same medication from different doctors
Key Insight: Decomposing into three binary relationships loses information. We need to know that Dr. Smith prescribed Aspirin to John on this date.
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071
-- Entity: Medical professionalsCREATE TABLE Doctor ( doctor_id INT PRIMARY KEY AUTO_INCREMENT, license_number VARCHAR(20) UNIQUE NOT NULL, full_name VARCHAR(100) NOT NULL, specialization VARCHAR(50), is_active BOOLEAN DEFAULT TRUE); -- Entity: PatientsCREATE TABLE Patient ( patient_id INT PRIMARY KEY AUTO_INCREMENT, medical_record_num VARCHAR(20) UNIQUE NOT NULL, full_name VARCHAR(100) NOT NULL, date_of_birth DATE NOT NULL, allergies TEXT -- Critical for prescriptions); -- Entity: MedicationsCREATE TABLE Medication ( medication_id INT PRIMARY KEY AUTO_INCREMENT, ndc_code VARCHAR(20) UNIQUE NOT NULL, -- National Drug Code generic_name VARCHAR(100) NOT NULL, brand_name VARCHAR(100), drug_class VARCHAR(50), requires_script BOOLEAN DEFAULT TRUE); -- Ternary Bridge Table: PrescriptionsCREATE TABLE Prescription ( prescription_id INT PRIMARY KEY AUTO_INCREMENT, -- Surrogate key for child refs doctor_id INT NOT NULL, patient_id INT NOT NULL, medication_id INT NOT NULL, -- Relationship attributes prescribed_date DATE NOT NULL DEFAULT (CURRENT_DATE), dosage VARCHAR(50) NOT NULL, -- e.g., "500mg twice daily" duration_days INT, refills_allowed INT DEFAULT 0, refills_remaining INT, pharmacy_notes TEXT, -- Ensure ternary uniqueness per date UNIQUE (doctor_id, patient_id, medication_id, prescribed_date), FOREIGN KEY (doctor_id) REFERENCES Doctor(doctor_id), FOREIGN KEY (patient_id) REFERENCES Patient(patient_id), FOREIGN KEY (medication_id) REFERENCES Medication(medication_id), CHECK (refills_allowed >= 0), CHECK (refills_remaining IS NULL OR refills_remaining <= refills_allowed), CHECK (duration_days IS NULL OR duration_days > 0)); -- Indexes for common query patternsCREATE INDEX idx_prescription_doctor ON Prescription(doctor_id, prescribed_date);CREATE INDEX idx_prescription_patient ON Prescription(patient_id, prescribed_date);CREATE INDEX idx_prescription_medication ON Prescription(medication_id); -- Child table: Prescription fill historyCREATE TABLE PrescriptionFill ( fill_id INT PRIMARY KEY AUTO_INCREMENT, prescription_id INT NOT NULL, pharmacy_id INT NOT NULL, fill_date DATE NOT NULL, quantity_dispensed INT NOT NULL, FOREIGN KEY (prescription_id) REFERENCES Prescription(prescription_id), FOREIGN KEY (pharmacy_id) REFERENCES Pharmacy(pharmacy_id));12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970
-- Sample DataINSERT INTO Doctor (license_number, full_name, specialization) VALUES('MD12345', 'Dr. Sarah Chen', 'Internal Medicine'),('MD67890', 'Dr. James Wilson', 'Cardiology'); INSERT INTO Patient (medical_record_num, full_name, date_of_birth, allergies) VALUES('MRN-001', 'John Doe', '1975-06-15', 'Penicillin'),('MRN-002', 'Jane Smith', '1988-02-20', NULL); INSERT INTO Medication (ndc_code, generic_name, brand_name, drug_class) VALUES('12345-6789', 'Lisinopril', 'Prinivil', 'ACE Inhibitor'),('98765-4321', 'Metformin', 'Glucophage', 'Antidiabetic'); INSERT INTO Prescription (doctor_id, patient_id, medication_id, dosage, duration_days, refills_allowed, refills_remaining) VALUES(1, 1, 1, '10mg once daily', 90, 3, 3),(2, 1, 2, '500mg twice daily', 30, 11, 11),(1, 2, 1, '5mg once daily', 90, 3, 3); -- Query 1: Patient's current prescriptions with doctor infoSELECT p.prescription_id, m.generic_name, m.brand_name, pr.dosage, d.full_name AS prescribing_doctor, pr.prescribed_date, pr.refills_remainingFROM Prescription prJOIN Doctor d ON pr.doctor_id = d.doctor_idJOIN Medication m ON pr.medication_id = m.medication_idJOIN Patient p ON pr.patient_id = p.patient_idWHERE p.patient_id = 1ORDER BY pr.prescribed_date DESC; -- Query 2: Medications a doctor commonly prescribesSELECT m.generic_name, COUNT(*) AS times_prescribed, COUNT(DISTINCT pr.patient_id) AS unique_patientsFROM Prescription prJOIN Medication m ON pr.medication_id = m.medication_idWHERE pr.doctor_id = 1GROUP BY m.medication_id, m.generic_nameORDER BY times_prescribed DESC; -- Query 3: Check for potential drug interactions-- (Simplified: find patients on multiple medications from same class)SELECT pt.full_name AS patient, GROUP_CONCAT(m.generic_name) AS medications, m.drug_classFROM Prescription prJOIN Patient pt ON pr.patient_id = pt.patient_idJOIN Medication m ON pr.medication_id = m.medication_idGROUP BY pr.patient_id, pt.full_name, m.drug_classHAVING COUNT(*) > 1; -- Query 4: Audit - who prescribed what to whom and whenSELECT pr.prescribed_date, d.full_name AS doctor, pt.full_name AS patient, m.generic_name AS medication, pr.dosageFROM Prescription prJOIN Doctor d ON pr.doctor_id = d.doctor_idJOIN Patient pt ON pr.patient_id = pt.patient_idJOIN Medication m ON pr.medication_id = m.medication_idORDER BY pr.prescribed_date DESCLIMIT 100;This example illustrates ternary relationship mapping. Real healthcare systems require HIPAA compliance, audit logging, encryption, role-based access control, and extensive validation. The schema shown is for educational purposes.
Domain: A content management system where articles can have multiple tags, and tags apply to multiple articles. This is a simple M:N with minimal relationship attributes.
ER Relationship:
Article ←(M)— HasTag —(N)→ Tag
• Attributes: tagged_at (when tag was applied)
• Cardinality: Articles may have 0-15 tags; Tags may have 0+ articles
Key Insight: Even 'simple' M:N relationships benefit from temporal tracking. Knowing when a tag was applied enables analytics on tagging behavior.
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061
-- Entity: Articles/PostsCREATE TABLE Article ( article_id INT PRIMARY KEY AUTO_INCREMENT, slug VARCHAR(200) UNIQUE NOT NULL, title VARCHAR(300) NOT NULL, content MEDIUMTEXT, author_id INT NOT NULL, status ENUM('draft', 'review', 'published', 'archived') DEFAULT 'draft', published_at TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (author_id) REFERENCES Author(author_id)); -- Entity: Tags (taxonomy)CREATE TABLE Tag ( tag_id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) UNIQUE NOT NULL, slug VARCHAR(50) UNIQUE NOT NULL, description VARCHAR(500), color CHAR(7), -- Hex color like #FF5733 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP); -- Bridge Table: Article-Tag associationsCREATE TABLE ArticleTag ( article_id INT NOT NULL, tag_id INT NOT NULL, -- Relationship attribute tagged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, tagged_by INT, -- User who applied the tag PRIMARY KEY (article_id, tag_id), FOREIGN KEY (article_id) REFERENCES Article(article_id) ON DELETE CASCADE, FOREIGN KEY (tag_id) REFERENCES Tag(tag_id) ON DELETE CASCADE, FOREIGN KEY (tagged_by) REFERENCES Author(author_id) ON DELETE SET NULL); -- Index for tag-centric queriesCREATE INDEX idx_articletag_tag ON ArticleTag(tag_id); -- Enforce maximum 15 tags per article (via trigger)DELIMITER //CREATE TRIGGER trg_max_tags_per_articleBEFORE INSERT ON ArticleTagFOR EACH ROWBEGIN DECLARE tag_count INT; SELECT COUNT(*) INTO tag_count FROM ArticleTag WHERE article_id = NEW.article_id; IF tag_count >= 15 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Maximum 15 tags per article allowed'; END IF;END //DELIMITER ;1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768
-- Sample DataINSERT INTO Tag (name, slug, color, description) VALUES('JavaScript', 'javascript', '#F7DF1E', 'JS language topics'),('Python', 'python', '#3776AB', 'Python language topics'),('Database', 'database', '#336791', 'Database design and SQL'),('Tutorial', 'tutorial', '#28A745', 'Step-by-step guides'),('Advanced', 'advanced', '#DC3545', 'Expert-level content'); INSERT INTO Article (slug, title, author_id, status, published_at) VALUES('intro-to-sql', 'Introduction to SQL', 1, 'published', NOW()),('python-db-access', 'Python Database Access', 1, 'published', NOW()),('advanced-joins', 'Advanced JOIN Techniques', 2, 'published', NOW()); INSERT INTO ArticleTag (article_id, tag_id) VALUES(1, 3), (1, 4), -- SQL intro: Database, Tutorial(2, 2), (2, 3), (2, 4), -- Python DB: Python, Database, Tutorial(3, 3), (3, 5); -- Joins: Database, Advanced -- Query 1: Get all tags for an articleSELECT t.name, t.color, at.tagged_atFROM ArticleTag atJOIN Tag t ON at.tag_id = t.tag_idWHERE at.article_id = 2ORDER BY t.name; -- Query 2: Find articles by tagSELECT a.title, a.slug, a.published_atFROM Article aJOIN ArticleTag at ON a.article_id = at.article_idJOIN Tag t ON at.tag_id = t.tag_idWHERE t.slug = 'database' AND a.status = 'published'ORDER BY a.published_at DESC; -- Query 3: Tag cloud with article countsSELECT t.name, t.slug, t.color, COUNT(at.article_id) AS article_countFROM Tag tLEFT JOIN ArticleTag at ON t.tag_id = at.tag_idLEFT JOIN Article a ON at.article_id = a.article_id AND a.status = 'published'GROUP BY t.tag_id, t.name, t.slug, t.colorORDER BY article_count DESC; -- Query 4: Find articles with ALL specified tags-- (Articles tagged with BOTH 'database' AND 'tutorial')SELECT a.article_id, a.titleFROM Article aJOIN ArticleTag at ON a.article_id = at.article_idJOIN Tag t ON at.tag_id = t.tag_idWHERE t.slug IN ('database', 'tutorial')GROUP BY a.article_id, a.titleHAVING COUNT(DISTINCT t.tag_id) = 2; -- Query 5: Related articles (share tags with given article)SELECT a2.article_id, a2.title, COUNT(*) AS shared_tagsFROM ArticleTag at1JOIN ArticleTag at2 ON at1.tag_id = at2.tag_id AND at1.article_id != at2.article_idJOIN Article a2 ON at2.article_id = a2.article_idWHERE at1.article_id = 1 -- Find articles related to article 1 AND a2.status = 'published'GROUP BY a2.article_id, a2.titleORDER BY shared_tags DESCLIMIT 5;Finding items with ALL specified tags (vs ANY) requires counting matches and comparing to the target count. This is a common interview question and real-world need for faceted search.
Domain: A project management system tracking employees, their skills, and certifications for those skills. This involves a chain of M:N relationships.
ER Relationships:
Employee ←(M:N)→ Skill (Employee has skills)
│
└── EmployeeSkill ←(M:N)→ Certification
(Employee's skill can be certified by multiple bodies)
Key Insight: The certification is about an employee's possession of a skill, not the skill itself. Certifications attach to the Employee-Skill relationship, not to Employee or Skill individually.
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677
-- Entity: EmployeesCREATE TABLE Employee ( employee_id INT PRIMARY KEY AUTO_INCREMENT, employee_code VARCHAR(20) UNIQUE NOT NULL, full_name VARCHAR(100) NOT NULL, email VARCHAR(255) UNIQUE NOT NULL, department_id INT, hire_date DATE NOT NULL); -- Entity: Skills catalogCREATE TABLE Skill ( skill_id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) UNIQUE NOT NULL, category VARCHAR(50), -- e.g., Technical, Leadership, Domain description TEXT); -- Entity: Certification bodies/credentialsCREATE TABLE Certification ( certification_id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(200) NOT NULL, -- e.g., "AWS Solutions Architect" issuing_body VARCHAR(100) NOT NULL, -- e.g., "Amazon Web Services" validity_months INT, -- NULL = never expires UNIQUE (name, issuing_body)); -- Bridge Table 1: Employee-Skill (M:N)CREATE TABLE EmployeeSkill ( employee_id INT NOT NULL, skill_id INT NOT NULL, -- Relationship attributes proficiency ENUM('beginner', 'intermediate', 'advanced', 'expert') NOT NULL DEFAULT 'beginner', years_experience DECIMAL(3,1), last_used_date DATE, acquired_at DATE, PRIMARY KEY (employee_id, skill_id), FOREIGN KEY (employee_id) REFERENCES Employee(employee_id) ON DELETE CASCADE, FOREIGN KEY (skill_id) REFERENCES Skill(skill_id) ON DELETE RESTRICT); -- Bridge Table 2: EmployeeSkill-Certification (M:N referencing M:N)-- This table references the bridge table aboveCREATE TABLE SkillCertification ( employee_id INT NOT NULL, -- Part of FK to EmployeeSkill skill_id INT NOT NULL, -- Part of FK to EmployeeSkill certification_id INT NOT NULL, -- Relationship attributes certified_date DATE NOT NULL, expiration_date DATE, -- Calculated from certification.validity_months credential_number VARCHAR(100), verification_url VARCHAR(500), PRIMARY KEY (employee_id, skill_id, certification_id), -- References the first bridge table FOREIGN KEY (employee_id, skill_id) REFERENCES EmployeeSkill(employee_id, skill_id) ON DELETE CASCADE, FOREIGN KEY (certification_id) REFERENCES Certification(certification_id) ON DELETE RESTRICT, CHECK (expiration_date IS NULL OR certified_date <= expiration_date)); -- IndexesCREATE INDEX idx_empskill_skill ON EmployeeSkill(skill_id);CREATE INDEX idx_skillcert_cert ON SkillCertification(certification_id);CREATE INDEX idx_skillcert_expiration ON SkillCertification(expiration_date);123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687
-- Sample DataINSERT INTO Skill (name, category) VALUES('Cloud Architecture', 'Technical'),('Project Management', 'Leadership'),('Python', 'Technical'),('Data Modeling', 'Technical'); INSERT INTO Certification (name, issuing_body, validity_months) VALUES('AWS Solutions Architect Professional', 'Amazon Web Services', 36),('PMP', 'Project Management Institute', 36),('Google Cloud Professional Data Engineer', 'Google', 24); INSERT INTO EmployeeSkill (employee_id, skill_id, proficiency, years_experience) VALUES(1, 1, 'expert', 5.0), -- Alice: Cloud Architecture expert(1, 2, 'advanced', 3.0), -- Alice: Project Management advanced(1, 3, 'intermediate', 2.0), -- Alice: Python intermediate(2, 1, 'advanced', 3.0), -- Bob: Cloud Architecture advanced(2, 4, 'expert', 6.0); -- Bob: Data Modeling expert INSERT INTO SkillCertification (employee_id, skill_id, certification_id, certified_date, expiration_date, credential_number) VALUES(1, 1, 1, '2023-06-15', '2026-06-15', 'AWS-SAP-12345'), -- Alice's AWS cert for Cloud skill(1, 2, 2, '2022-01-10', '2025-01-10', 'PMP-67890'), -- Alice's PMP for PM skill(2, 1, 1, '2024-03-01', '2027-03-01', 'AWS-SAP-99999'); -- Bob's AWS cert for Cloud skill -- Query 1: Employee's complete skill and certification profileSELECT e.full_name, s.name AS skill, es.proficiency, c.name AS certification, sc.certified_date, sc.expiration_date, CASE WHEN sc.expiration_date < CURRENT_DATE THEN 'Expired' WHEN sc.expiration_date < DATE_ADD(CURRENT_DATE, INTERVAL 90 DAY) THEN 'Expiring Soon' ELSE 'Active' END AS cert_statusFROM Employee eJOIN EmployeeSkill es ON e.employee_id = es.employee_idJOIN Skill s ON es.skill_id = s.skill_idLEFT JOIN SkillCertification sc ON es.employee_id = sc.employee_id AND es.skill_id = sc.skill_idLEFT JOIN Certification c ON sc.certification_id = c.certification_idWHERE e.employee_id = 1ORDER BY s.name, c.name; -- Query 2: Find employees with specific certified skillSELECT DISTINCT e.employee_id, e.full_name, es.proficiencyFROM Employee eJOIN EmployeeSkill es ON e.employee_id = es.employee_idJOIN Skill s ON es.skill_id = s.skill_idJOIN SkillCertification sc ON es.employee_id = sc.employee_id AND es.skill_id = sc.skill_idWHERE s.name = 'Cloud Architecture' AND sc.expiration_date > CURRENT_DATE; -- Query 3: Certifications expiring within 90 daysSELECT e.full_name, e.email, s.name AS skill, c.name AS certification, sc.expiration_date, DATEDIFF(sc.expiration_date, CURRENT_DATE) AS days_until_expiryFROM SkillCertification scJOIN Employee e ON sc.employee_id = e.employee_idJOIN Skill s ON sc.skill_id = s.skill_idJOIN Certification c ON sc.certification_id = c.certification_idWHERE sc.expiration_date BETWEEN CURRENT_DATE AND DATE_ADD(CURRENT_DATE, INTERVAL 90 DAY)ORDER BY sc.expiration_date; -- Query 4: Skills with certified vs uncertified countSELECT s.name AS skill, COUNT(DISTINCT es.employee_id) AS employees_with_skill, COUNT(DISTINCT sc.employee_id) AS employees_certified, ROUND(100.0 * COUNT(DISTINCT sc.employee_id) / COUNT(DISTINCT es.employee_id), 1) AS cert_rate_pctFROM Skill sLEFT JOIN EmployeeSkill es ON s.skill_id = es.skill_idLEFT JOIN SkillCertification sc ON es.employee_id = sc.employee_id AND es.skill_id = sc.skill_id AND sc.expiration_date > CURRENT_DATEGROUP BY s.skill_id, s.nameORDER BY cert_rate_pct DESC;SkillCertification references EmployeeSkill, which is itself a bridge table. This creates a two-level M:N structure. The composite FK (employee_id, skill_id) in SkillCertification references the composite PK of EmployeeSkill. This pattern is powerful for modeling nuanced real-world scenarios.
Across all five examples, consistent patterns emerge. This summary consolidates the key design decisions and their rationale.
| Decision Point | Guideline | Examples |
|---|---|---|
| Primary Key | Use composite (FK1, FK2) unless child tables need single-column ref | OrderItem, Follow, ArticleTag use composite; Prescription uses surrogate (has children) |
| Naming | Use domain term if clear; concatenate if not | Enrollment, Prescription, Follow (semantic) vs ArticleTag, EmployeeSkill (concatenated) |
| Temporal Tracking | Always include created_at; add period columns if history needed | All examples have timestamps; Prescription has fill history |
| CASCADE vs RESTRICT | CASCADE for dependent relationships; RESTRICT if deletion should be blocked | Most use CASCADE; Product-Order uses RESTRICT on product deletion |
| Relationship Attributes | Store only data dependent on BOTH entities | OrderItem: quantity, unit_price; EmployeeSkill: proficiency, years |
| Constraints | Enforce domain rules via CHECK, ENUM, triggers | Quantity > 0, self-follow prevention, max tags trigger |
| Indexing | Always add reverse index on second FK column | All examples include index on second component |
These patterns are production-grade. Each example addresses real-world concerns: data integrity through constraints, query performance through indexing, and domain semantics through appropriate attribute placement.
You have completed the comprehensive module on mapping many-to-many relationships from ER diagrams to relational schemas. Let's consolidate everything we've covered across all five pages.
The Complete M:N Mapping Workflow:
1. ANALYZE: Identify M:N relationship in ER diagram
2. CREATE BRIDGE TABLE: Name appropriately, add FK columns
3. DEFINE COMPOSITE PK: Combine FKs (or add surrogate if needed)
4. ADD FOREIGN KEY CONSTRAINTS: Choose CASCADE/RESTRICT per side
5. IDENTIFY RELATIONSHIP ATTRIBUTES: Apply dependency test
6. ADD ATTRIBUTE COLUMNS: With appropriate constraints
7. CREATE REVERSE INDEX: On second FK column
8. ADD DOMAIN CONSTRAINTS: CHECK, ENUM, triggers as needed
9. WRITE CORE QUERIES: Verify design with typical operations
10. DOCUMENT: Schema, constraints, and query patterns
You now possess expert-level knowledge of many-to-many relationship mapping. This skill is fundamental to professional database design—virtually every non-trivial database contains M:N relationships, and your ability to map them correctly determines the integrity, performance, and maintainability of the resulting system.
What's next in Chapter 8:
Having mastered entity mapping, 1:1, 1:N, and M:N relationship mapping, the final module covers Specialization Mapping—translating inheritance hierarchies (supertype/subtype relationships) from EER diagrams to relational schemas using single-table, multi-table, or hybrid approaches.