Digital Land View model

model.txt

// https://dbdiagram.io/d/60536fd6ecb54e10c33c1b72
// > many-to-one; < one-to-many; - one-to-one

// "organisation", "geography", "category", "document", "policy", "rule"
Table typology as T {
  typology varchar [pk]
  name varchar
}

// "local-authority", "conservation-area", "brownfield-land", etc
Table prefix as X {
  prefix varchar [pk]
  typology varchar
}
Ref: X.typology > T.typology

// stem of the slug:
//   "/local-authority/"
//   "/conservation-area/local-authority/SWK/"
Table slug as S {
  id int [pk]
  prefix varchar
  slug varchar
}
Ref: S.prefix > P.prefix

Table category as C {
  id int [pk]
  slug int
  category varchar
  reference varchar
  name varchar
}
Ref: C.slug > S.id

Table rule as R {
  id int [pk]
  slug int
  rule varchar
  reference varchar
  name varchar
  description varchar
  legislation int
}
Ref: R.slug > S.id

Table rule_category as RC {
  rule int
  category int
}
Ref: RC.rule > R.id
Ref: RC.category > C.id

Table policy as P {
  id int [pk]
  prefix varchar
  category varchar
  reference varchar
  name varchar
}

Table policy_rule as PR {
  policy int
  rule int
  start_date datetime
  end_date datetime
}
Ref: PR.rule > R.id
Ref: PR.policy > P.id

Table document as D {
  id int [pk]
  prefix varchar
  document varchar
  reference varchar
  name varchar
  continent_name varchar
  start_date datetime
  end_date datetime
}

Table policy_document as PD {
  policy int
  document int
}
Ref: PD.policy > D.id
Ref: PD.document > P.id

Table geography as G {
  id int [pk]
  slug int
  geography varchar
  geometry varchar
  name varchar
  document int
  start_date datetime
  end_date datetime
}
Ref: G.slug > S.id
Ref: G.document > D.id

Table policy_geograpy as PG {
  policy int
  geography int
  start_date datetime
  end_date datetime
}
Ref: PG.policy > P.id
Ref: PG.geography > G.id

Table organisation as O {
  id int [pk]
  prefix varchar // "local-authority"
  organisation varchar // "DAC"
  reference varchar
  name varchar
  start_date datetime
  end_date datetime
}

Table organisation_member as OM {
  organisation int
  member int
  start_date datetime
  end_date datetime
}
Ref: OM.organisation > O.id
Ref: OM.member > O.id

Table organisation_category as OC {
  organisation int
  category int
  start_date datetime
  end_date datetime
}
Ref: OC.organisation > O.id
Ref: OC.category > C.id

Table organisation_geography as OG {
  organisation int
  geography int
  start_date datetime
  end_date datetime
}
Ref: OG.organisation > O.id
Ref: OG.geography > G.id