ICT:DBML - version 5.0

// =====================================================
// Costasano Heritage Database Model — v5.0 
// =====================================================

// ==== HERITAGE OBJECTS ====

Table Object {
  Code string [pk]
  Parent string [ref: > Object.Code]
  Name string
  Type string
  Subtype string
  Place string [ref: > Place.Code]
  DateFrom date
  DateTo date
  Description text
  Notes text
}

// ==== DIGITAL ASSETS ====

Table Asset {
  Caption string
  Code string [pk]
  Chapter string [ref: > Chapter.Code]
  Place string [ref: > Place.Code]
  Organisation string [ref: > Organisation.Code]
  Sequence int
  MediaFile string [ref: > Files.id]
  Parent string [ref: > Asset.Code]
  AssetSourceType string [ref: > AssetSourceType.Code]
  AssetType string [ref: > AssetType.Code]
  SourceReference string
  Description text
  Notes text
  AiProcessed boolean
  Citation text
  Permalink string
  Repository string
  Rights string
  IsPublishable boolean
}

// ==== EXTERNAL DRUPAL FILES ====

Table Files {
  id string [pk]
  Label string
  Timestamp datetime
  User string
  Comment text
  Size int
  Sha1 string
  Mime string
  Url string
}

// ==== RESEARCH ENTITIES ====

Table Person {
  Code string [pk]
  FirstName string
  LastName string
  BirthDate date
  DeathDate date
  Biography text
  Contact text
  Notes text
}

Table Organisation {
  Code string [pk]
  Name string
  Place string [ref: > Place.Code]
  Description text
  Contact text
  Notes text
}

Table Place {
  Code string [pk]
  Parent string [ref: > Place.Code]
  Label string
  Type string
  Latitude float
  Longitude float
  Description text
  Notes text
}

Table Chapter {
  Code string [pk]
  Description string
  Parent string [ref: > Chapter.Code]
  StartYear integer
  EndYear integer
  Notes text
}

// ==== LOOKUP TABLES ====

Table ObjectType {
  Code string [pk]
  Description text
}

Table AssetSourceType {
  Code string [pk]
  Label string
  Description text
}

Table AssetType {
  Code string [pk]
  Name string
  Description text
}

Table AssetRole {
  Code string [pk]
  Name string
  Description text
}

Table PersonRole {
  Code string [pk]
  Name string
  Description text
}

Table OrganisationRole {
  Code string [pk]
  Name string
  Description text
}

Table Keywords {
  Code string [pk]
  Name string
  Description text
}

// ==== RELATIONSHIP TABLES (Junctions) ====

Table ObjectAsset {
  id string [pk]
  Object string [ref: > Object.Code]
  Asset string [ref: > Asset.Code]
  Role string [ref: > AssetRole.Code]
  IsPreferred boolean
}

Table PersonAsset {
  id string [pk]
  Person string [ref: > Person.Code]
  Asset string [ref: > Asset.Code]
}

Table OrganisationAsset {
  id string [pk]
  Organisation string [ref: > Organisation.Code]
  Asset string [ref: > Asset.Code]
}

Table OjectChapter {
  id string [pk]
  Object string [ref: > Object.Code]
  Chapter string [ref: > Chapter.Code]
}

Table ObjectPerson {
  id string [pk]
  Object string [ref: > Object.Code]
  Person string [ref: > Person.Code]
  Role string [ref: > PersonRole.Code]
}

Table ObjectOrganisation {
  id string [pk]
  Object string [ref: > Object.Code]
  Organisation string [ref: > Organisation.Code]
  Role string [ref: > OrganisationRole.Code]
}

Table PersonOrganisationRole {
  id string [pk]
  Person string [ref: > Person.Code]
  Organisation string [ref: > Organisation.Code]
  Role string [ref: > OrganisationRole.Code]
}

Table ObjectKeyword {
  id string [pk]
  Object string [ref: > Object.Code]
  Keyword string [ref: > Keywords.Code]
}

Table ObjectHolder {
  id string [pk]
  Object string [ref: > Object.Code]
  Person string [ref: > Person.Code]
  Organisation string [ref: > Organisation.Code]
}