Index: data/csv/db_experiment/v0.2/db_docs.md ================================================================== --- data/csv/db_experiment/v0.2/db_docs.md +++ data/csv/db_experiment/v0.2/db_docs.md @@ -37,11 +37,12 @@ - **`ID`** *(int, PK)*: Unique identifier for each toponym entry, independent from the spatial table. - **`SPATID`** *(int, FK → Spatial.ID)*: Links the toponym to its corresponding spatial object. - **`DOUSPAT`** *(int, nullable)*: Certainty of name relation to the spatial object (empty = "I'm sure", 1 = "The spatial link is doubtful"). - **`MAINID`** *(int, FK → Linguistic.ID)*: Represents the "main" name in a "nest" of interconnected toponyms (sometimes grouping is necessary, many sources give several somehow connected names for a single spatial object and we want to reflect this connections in database). - **`TOPONYM`** *(string)*: The attested name as recorded from sources. -- **`DOUTOPO`** *(int, nullable)*: Certainty of the given orthographic form (empty = "I'm sure", 1 = "It is doubtful"). +- **`TOPFORMS`** *(text, nullable)*: Holds different attested spelling variations of the name from multiple sources. +- **`DOUTOPO`** *(int, nullable)*: Certainty of the given **`TOPONYM`** form (empty = "I'm sure", 1 = "It is doubtful"). - **`LANG`** *(string)*: The language of the toponym, following the predefined codes. - **`DOULANG`** *(int, nullable)*: Certainty of the language of the toponym (empty = "I'm sure", 1 = "It is doubtful"). - **`PRONUNC`** *(text, nullable)*: Reserved for pronunciation information (format yet to be decided). - **`DOUPRON`** *(int, nullable)*: Certainty of the pronunciation information (empty = "I'm sure", 1 = "It is doubtful"). - **`ETYM`** *(text, nullable)*: Etymological explanation of the name's origin. It is always doubtful. @@ -57,11 +58,10 @@ This table records historical changes in toponyms, tracking their usage over time. ### **Columns:** - **`ID`** *(int, PK)*: Unique identifier for each temporal record. - **`LINGID`, `LINGNAME`** *(Composite FK → Linguistic (ID, TOPONYM))*: Together, these fields reference a specific toponym in the linguistic table. -- **`TOPFORMS`** *(text, nullable)*: Holds different attested spelling variations of the name from multiple sources. - **`START`** *(int)*: Earliest recorded use of the name (source text goes to **`FULLTEXT`** column). - **`DOUSTART`** *(int)*: Certainty level for the `START` date (empty = "I'm sure", 1 = "It is doubtful"). - **`END`** *(int, nullable)*: The last recorded use of the name. If the object still exists, this field remains empty. The source text is stored in the **FULLTEXT** column. - **`DOUEND`** *(int, nullable)*: Certainty level for the `END` date (empty = "I'm sure", 1 = "It is doubtful"). - **`EVENT`** *(string)*: Categorization of historical change, with predefined values: @@ -87,7 +87,6 @@ --- ## **Next Steps** - Finalize the format for the `PRONUNC` field. - - +- Make a separate table for the list of written sources Index: data/csv/db_experiment/v0.2/db_structure.sql ================================================================== --- data/csv/db_experiment/v0.2/db_structure.sql +++ data/csv/db_experiment/v0.2/db_structure.sql @@ -17,10 +17,11 @@ ID INT PRIMARY KEY, SPATID INT NOT NULL, DOUSPAT INT CHECK (DOUSPAT IN (1)) NULL, MAINID INT, TOPONYM TEXT NOT NULL, + TOPFORMS TEXT, DOUTOPO INT CHECK (DOUTOPO IN (1)) NULL, LANG VARCHAR(3) NOT NULL, DOULANG INT CHECK (DOULANG IN (1)) NULL, PRONUNC TEXT, DOUPRON INT CHECK (DOUPRON IN (1)) NULL, @@ -34,11 +35,10 @@ CREATE TABLE temporal ( ID INT PRIMARY KEY, LINGID INT NOT NULL, LINGNAME TEXT NOT NULL, - TOPFORMS TEXT, START INT NOT NULL, DOUSTART INT CHECK (DOUSTART IN (1)) NULL, END INT, DOUEND INT CHECK (DOUEND IN (1)) NULL, EVENT TEXT CHECK (EVENT IN ('MERGEIN', 'ACTIVE', 'RENAME', 'CEASE')),