MySQL Design Patterns

🧠 Free Recall ❓ Quiz 🔍 Cues 🔮 Simulation 🥷 ShadowBox 📐 Encode Cards

Patterns for describing parent-child relationships of data

Parent_id column
Hold all parent and child rows in the same table. Add a parent_id column to the table. Child data refers to the parent via the column. Allows for multi-level childs

Child Table
Hold all child rows in an entire table and refer to the parent table using a parent_id column. Use if only 1 level is a constraint and parent's and child's are accessed as different entities

Path Enumeration
Create a string column that describes a path of the relationship using a delimiter

id, path, author, comment
1 1/ Fran What’s the cause of this bug? 
2 1/2/ Ollie I think it’s a null pointer. 
3 1/2/3/ Fran No, I checked for that. 
4 1/4/ Kukla We need to check for invalid input. 
5 1/4/5/ Ollie Yes, that’s a bug. 
6 1/4/6/ Fran Yes, please add a check.

Is also at the same time an anti-pattern of comma separated lists

Closure Table

A closure table is a table to hold entire tree relationship in a separate table from the entities. In the closure table relationships are described by ancestor and descendent rows. Every entity has a self-referencing tree. Then from there every ancestor and every descendent is described in the table in relation to specific entities.

For example using my own family

A / D
Grandma Gladys -- Grandma Gladys
Ihuoma -- Ihuoma
Grandma Gladys -- Ihuoma
Jr -- Jr
Grandma Gladys -- Jr
Ihuoma -- Jr
Dodo -- Dodo
Grandma Gladys -- Dodo
Ihuoma -- Dodo
Phoenix -- Phoenix
Grandma Gladys -- Phoenix
Ihuoma -- Phoenix
Jr -- Phoenix 
PJR -- PJR
Grandma Gladys -- PJR
Ihuoma -- PJR
Jr -- PJR
Phoenix -- PJR

Understanding the CRUD of closure table data

Create
Create self referencing row
If ancestors
Copy all ancestor rows of direct parent, but replace descendent column with new insert id
Read
To find all parents of an entity
Join entities on a.id where d.id = entity_id
To find all descendants 
Join entities on d.id where a.id = entity_id
Update
To move a subtree
Remove all where d.id = entity_id and a.id != entity_id
Copy all ancestors of new ancestor but replace d.id with entity_id 
Update subtrees of the subtree
Find all the subtrees of first subtree. Remove all ancestor tree relationship except where sub-subtree.id = sub-subtree.id and where a.id = subtree-entity.id
Then copy all the ancestor rows of subtree but replace d.id = sub-subtree-entity.id
Delete
To delete a leaf node, delete all that refer to the entity as a descendant
To delete a subtree delete all that refer to entity as an ancestor

A visual guide for moving subtrees (Dodo adopts Phoenix)
-------
Grandma Gladys -- Grandma Gladys
Ihuoma -- Ihuoma
Grandma Gladys -- Ihuoma
Jr -- Jr
Grandma Gladys -- Jr
Ihuoma -- Jr
Dodo -- Dodo
Grandma Gladys -- Dodo
Ihuoma -- Dodo
Phoenix -- Phoenix
Grandma Gladys -- Phoenix
Ihuoma -- Phoenix
Jr -- Phoenix 
PJR -- PJR
Grandma Gladys -- PJR
Ihuoma -- PJR
Jr -- PJR
Phoenix -- PJR
Dodo -- Phoenix
Grandma Gladys  -- Phoenix
Ihuoma -- Phoenix 
Dodo -- PJR
Grandma Gladys  -- PJR
Ihuoma -- PJR

Patterns to describe an entities attributes

Concrete Table Inheritance

Create separate tables for the subtypes of an entity. For example an Issues entity might have different attributes whether it's a bug or a feature request, so why not create separate tables for each instead of one table named issues

If you often use the two entity types together then create a view that unions them with the needed columns.

Class Table Inheritance

Create a table named Issues. But then create subtables named bug and feature request which refer to the issue_id in the Issue table. That way when you only need issues, it's easier.

Concrete SubEntities

Group attributes into concrete sub-entities for example if you have property data which can have many attributes you can start separating information into attributes such as property_mortgage_table

Unstarred

Dunk (exclude from study)

MySQL Design Patterns

Review Questions

Todos

Sensemaker