ICT:DBML - version 4.0

// =====================================================
// Costasano Heritage Database Model — v4.0
// Adds Places entity for explicit spatial modeling
// Separates actors (Persons/Organisations) from locations
// =====================================================



// ============================
// CORE ENTITIES
// ============================

Table HeritageObjects {
  id string [pk]
  parent_id string [ref: > HeritageObjects.id]

  title string
  type_id string [ref: > HeritageObjectTypes.id]
  subtype string

  place_id string [ref: > Places.id] // NEW — location

  date_from datetime
  date_to datetime

  description text
  language string
  notes text
}



// ============================
// Digital Assets
// ============================

Table DigitalAssets {
  id string [pk]

  // ---- storage ----
  file_id string [ref: > MediaWikiFiles.id]

  // ---- recursion ----
  parent_id string [ref: > DigitalAssets.id]

  // ---- classification ----
  asset_source_type_id string [ref: > AssetSourceTypes.id]
  asset_type_id string [ref: > AssetTypes.id]

  // ---- spatial context ----
  place_id string [ref: > Places.id] // NEW — place of creation/depiction

  asset_source_reference string
  language string
  description text
  notes text
  ai_processed boolean

  citation_text text
  permalink string
  repository string
  rights string
  is_publishable boolean
}



// ============================
// EXTERNAL (MediaWiki)
// ============================

Table MediaWikiFiles {
  id string [pk]
  title string
  timestamp datetime
  user string
  comment text
  size int
  sha1 string
  mime string
  url string
}



// ============================
// Research Entities
// ============================

Table Persons {
  id string [pk]
  firstname string
  lastname string
  birth_date datetime
  death_date datetime
  biography text
  contact_information text
  notes text
}



Table Organisations {
  id string [pk]

  name string
  description text

  place_id string [ref: > Places.id] // NEW — located in

  organisations_ref string
  contact_information text
  notes text
}



// ============================
// NEW — PLACES
// ============================

Table Places {
  id string [pk]
  parent_id string [ref: > Places.id] // hierarchy

  name string
  type string // city, site, building, region, country

  latitude float
  longitude float

  notes text
}



// ============================
// Narrative
// ============================

Table ResearchChapters {
  id string [pk]
  parent_id string [ref: > ResearchChapters.id]

  title string
  description text
  sequence_number int

  start_datetime datetime
  end_datetime datetime
  notes text
}



// ============================
// LOOKUPS
// ============================

Table HeritageObjectTypes {
  id string [pk]
  type_name string
  description text
}

Table AssetSourceTypes {
  id string [pk]
  source_type_name string
  description text
}

Table AssetTypes {
  id string [pk]
  asset_type_name string
  description text
}

Table DigitalAssetRoles {
  id string [pk]
  role_name string
  description text
}

Table PersonRoles {
  id string [pk]
  role_name string
  description text
}

Table OrganisationRoles {
  id string [pk]
  role_name string
  description text
}

Table Keywords {
  id string [pk]
  keyword string
  description text
}



// ============================
// RELATIONSHIPS
// ============================

Table HeritageObjectDigitalAssets {
  id string [pk]
  heritageobject_id string [ref: > HeritageObjects.id]
  digitalasset_id string [ref: > DigitalAssets.id]
  role_id string [ref: > DigitalAssetRoles.id]
  is_preferred boolean
}

Table PersonDigitalAssets {
  id string [pk]
  person_id string [ref: > Persons.id]
  digitalasset_id string [ref: > DigitalAssets.id]
}

Table OrganisationDigitalAssets {
  id string [pk]
  organisation_id string [ref: > Organisations.id]
  digitalasset_id string [ref: > DigitalAssets.id]
}



Table HeritageObjectChapters {
  id string [pk]
  heritageobject_id string [ref: > HeritageObjects.id]
  researchchapter_id string [ref: > ResearchChapters.id]
}



Table HeritageObjectPersons {
  id string [pk]
  heritageobject_id string [ref: > HeritageObjects.id]
  person_id string [ref: > Persons.id]
  role_id string [ref: > PersonRoles.id]
}

Table HeritageObjectOrganisations {
  id string [pk]
  heritageobject_id string [ref: > HeritageObjects.id]
  organisation_id string [ref: > Organisations.id]
  role_id string [ref: > OrganisationRoles.id]
}

Table PersonOrganisationRoles {
  id string [pk]
  person_id string [ref: > Persons.id]
  organisation_id string [ref: > Organisations.id]
  role_id string [ref: > OrganisationRoles.id]
}



Table HeritageObjectKeywords {
  id string [pk]
  heritageobject_id string [ref: > HeritageObjects.id]
  keyword_id string [ref: > Keywords.id]
}



Table HeritageObjectHolders {
  id string [pk]
  heritageobject_id string [ref: > HeritageObjects.id]
  person_id string [ref: > Persons.id]
  organisation_id string [ref: > Organisations.id]
}