### **Columns:**
- **`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.
- **`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.
- **`ORIGIN`** *(string, nullable)*: Language from which the name originated (e.g., `CHU`, `RUS`, `TAT`, `OTH`, `UNK`). This one is always doubtful too.
- **`COMMENTS`** *(text, nullable)*: Free-text notes.
- **`OTHER`** *(text, nullable)*: Reserved for future use.

---

## **3. Temporal Table**

### **Purpose:**
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.

- **`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:
  - `MERGEIN`: The object was absorbed into another.
  - `ACTIVE`: The name is still in use.

- **`LINGID` (Temporal) → `ID` (Linguistic)** and **`LINGNAME` (Temporal) → `TOPONYM` (Linguistic)**: Tracks historical changes of a specific name.
- **`OBJID` (Temporal) → `ID` (Linguistic)** and **`OBJNAME` (Temporal) → `TOPONYM` (Linguistic)**: Records specifically name changes or mergers.

---

## **Next Steps**
- Finalize the format for the `PRONUNC` field.
- Make a separate table for the list of written sources

CREATE TABLE linguistic (
    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,
    ETYM TEXT, -- Always doubtful
    ORIGIN VARCHAR(3), -- Always doubtful
    COMMENTS TEXT,
    OTHER TEXT,
    FOREIGN KEY (SPATID) REFERENCES spatial(ID),
    FOREIGN KEY (MAINID) REFERENCES linguistic(ID)
);

CREATE TABLE temporal (
    ID INT PRIMARY KEY,
    LINGID INT NOT NULL,
    LINGNAME TEXT NOT NULL,

    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')),
    OBJID INT,
    OBJNAME TEXT,
    FULLTEXT TEXT NOT NULL,
    COMMENTS TEXT,
    OTHER TEXT,
    FOREIGN KEY (LINGID, LINGNAME) REFERENCES linguistic(ID, TOPONYM),
    FOREIGN KEY (OBJID, OBJNAME) REFERENCES linguistic(ID, TOPONYM)
);