Реляционная схема базы данных (conferences.db)
Нормализованная база данных SQLite использует строгие ссылочные связи для сопоставления площадок, дат, залов заседаний, сессий, докладчиков и тем научных сообщений.
-- SQLite normalized database schema CREATE TABLE event_series ( event_series_id INTEGER PRIMARY KEY AUTOINCREMENT, series_name_en TEXT NOT NULL UNIQUE, series_name_ru TEXT, notes TEXT ); CREATE TABLE place ( place_id TEXT PRIMARY KEY, address_text TEXT, city TEXT, region TEXT, country TEXT, postal_code TEXT, latitude REAL, longitude REAL, source_url TEXT, notes TEXT ); CREATE TABLE organization ( organization_id TEXT PRIMARY KEY, display_name TEXT NOT NULL UNIQUE, display_name_ru TEXT, org_type TEXT, parent_org_id TEXT, source_url TEXT, notes TEXT ); CREATE TABLE venue ( venue_id TEXT PRIMARY KEY, display_name TEXT NOT NULL UNIQUE, venue_type TEXT, organization_id TEXT, place_id TEXT, source_url TEXT, notes TEXT, FOREIGN KEY(organization_id) REFERENCES organization(organization_id), FOREIGN KEY(place_id) REFERENCES place(place_id) ); CREATE TABLE event ( event_id TEXT PRIMARY KEY, event_series_id INTEGER, ordinal_int INTEGER, ordinal_roman TEXT, year INTEGER NOT NULL, theme_ru TEXT, theme_en TEXT, start_date TEXT, end_date TEXT, format TEXT DEFAULT 'unspecified', is_online INTEGER DEFAULT 0, online_platform TEXT, program_post_id TEXT, source_url TEXT NOT NULL, notes TEXT, FOREIGN KEY(event_series_id) REFERENCES event_series(event_series_id) ); CREATE TABLE event_day ( event_day_id TEXT PRIMARY KEY, event_id TEXT, day_number INTEGER NOT NULL, calendar_date TEXT, day_label_raw TEXT, source_url TEXT NOT NULL, notes TEXT, FOREIGN KEY(event_id) REFERENCES event(event_id) ); CREATE TABLE event_day_venue ( event_day_venue_id TEXT PRIMARY KEY, event_day_id TEXT, venue_id TEXT, occurrence_order INTEGER NOT NULL DEFAULT 1, room_text_raw TEXT, time_text_raw TEXT, source_url TEXT NOT NULL, source_snippet TEXT, FOREIGN KEY(event_day_id) REFERENCES event_day(event_day_id), FOREIGN KEY(venue_id) REFERENCES venue(venue_id) ); CREATE TABLE person ( person_id TEXT PRIMARY KEY, display_name TEXT NOT NULL UNIQUE, normalized_key TEXT, source_url TEXT, notes TEXT ); CREATE TABLE session ( session_id TEXT PRIMARY KEY, event_day_venue_id TEXT, session_title TEXT, session_type TEXT, start_time TEXT, end_time TEXT, time_text_raw TEXT, chair_text_raw TEXT, source_url TEXT NOT NULL, source_snippet TEXT, notes TEXT, FOREIGN KEY(event_day_venue_id) REFERENCES event_day_venue(event_day_venue_id) ); CREATE TABLE presentation ( presentation_id TEXT PRIMARY KEY, session_id TEXT, title TEXT, abstract TEXT, language TEXT DEFAULT 'unspecified', keywords TEXT, is_online INTEGER DEFAULT 0, order_in_session INTEGER, source_url TEXT NOT NULL, source_snippet TEXT, notes TEXT, FOREIGN KEY(session_id) REFERENCES session(session_id) ); CREATE TABLE presentation_person ( presentation_id TEXT, person_id TEXT, role TEXT DEFAULT 'unspecified', author_order INTEGER, affiliation_text_raw TEXT, organization_id TEXT, source_url TEXT NOT NULL, notes TEXT, PRIMARY KEY (presentation_id, person_id, role), FOREIGN KEY(presentation_id) REFERENCES presentation(presentation_id), FOREIGN KEY(person_id) REFERENCES person(person_id), FOREIGN KEY(organization_id) REFERENCES organization(organization_id) );