Brimbox Logo Brimbox Version 2.3.4 Released

Relationships>>

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_type is always on the left (join1).

Related relationships follow these rules:

  • Any layout can have up to 6 related relationships.
  • A related key is stored within the data_table data fields.

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

Join query

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

Related Query

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

Updated: 2017-08-29

Documents

Definitions