Brimbox primarily stores all its data in one table with different types of rows. These psuedo-tables are called layouts and there are three normalized relationships in Brimbox. Working with the self-relational
data_table typically involves self joins. There is also the
join_table which stores keys in many to many, or join, relationships.
The three types of relationships Brimbox supports are:
- Parent Child relationships (One to Many Relationships).
- Join relationships (Many to Many Relationships).
- Related relationships (Many to One Relationships).
Parent Child relationships follow these rules:
- Multiple parent layouts are allowed.
- A layout can have multiple child layouts.
- A child layout can have only one parent.
Join relationships follow these rulee:
- Any layout can be joined to another layout.
- In a join relationship, 2 rows can only be joined once.
- Unless a layout is joined to itself, the layout with the lessor
row_typeis always on the left (
Related relationships follow these rules:
- Any layout can have up to 6 related relationships.
- A related key is stored within the
These are the relationships in the sample data:
- Animals to Expenses has a parent child relationship.
- Animals to Owners has a join relationship.
- Animals to Breeds has a related relationship.
Here are explicit queries for the sample data.
Parent child query:
SELECT T1.c01 as name, T1.c02 as type, T2.c01 as topic, T2.c02 as cost FROM (SELECT * FROM data_table WHERE row_type = 1) T1 INNER JOIN (SELECT * FROM data_table WHERE row_type = 2) T2 ON T1.id = T2.key1
SELECT T1.c01 as name, T1.c02 as type, T3.c01 as Owner, T3.c06 as city FROM (SELECT * FROM data_table WHERE row_type = 1) T1 INNER JOIN join_table T2 ON T1.id = T2.join1 INNER JOIN (SELECT * FROM data_table WHERE row_type = 4) T3 ON T3.id = T2.join2
SELECT T1.c01 as name, T1.c02 as type, bb_value(T1.c41) as breed, T2.c02 as origination FROM (SELECT * FROM data_table WHERE row_type = 1) T1 INNER JOIN (SELECT * FROM data_table WHERE row_type = 3) T2 ON bb_key(T1.c41) = T2.id