RU:Catalog/Zverik/SQL

Хотя основным форматом каталога заявлен JSON, на деле базу потребуется хранить в какой-нибудь СУБД. Соответственно, должно быть отражение формата один к одному в формат SQL.

create table features (
  feature_id unsigned int primary key,
  feature_type smallint not null, -- 0: feature, 1: group, 2: class
  name varchar(255) not null,
  applies unsigned smallint,
  deprecated smallint not null
);

create table members (
  member_id unsigned int primary key,
  feature_id unsigned int not null,
  role varchar(127) not null,
  name varchar(255),
  applies smallint not null,
  minimum smallint not null,
  maximum smallint
);

create table attributes (
  attribute_id unsigned int primary key,
  feature_id unsigned int not null,
  key varchar(255) not null,
  name varchar(255),
  important smallint not null,
  value_type varchar(10) not null,
  range_min int,
  range_max int,
  range_step int,
  default varchar(255)
);

create table attribute_values (
  attribute_value_id unsigned int not null,
  attribute_id unsigned int not null,
  value varchar(255) not null
);

create table suffixes (
  suffix_id unsigned int primary key,
  name varchar(255) not null
);

create table suffix_values (
  suffix_value_id unsigned int not null,
  suffix_id unsigned int not null,
  value varchar(63) not null
);

-- Связывающие таблицы

create table feature_feature (
  child_id unsigned int not null,
  parent_id unsigned int not null,
  primary key (child_id, parent_id)
);

create table feature_tags (
  feature_id unsigned int not null,
  k varchar(255) not null,
  v varchar(255) not null,
  primary key (feature_id, k)
);

create table related (
  feature_id unsigned int not null,
  related_id unsigned int not null,
  primary key (feature_id, related_id)
);

-- Словарь

create table dictionaries (
  dictionary_id unsigned int primary key,
  language varchar(15) not null,
  name varchar(127) not null
);

create table dict_entries (
  dictionary_id unsigned int not null,
  feature_id unsigned int not null,
  entry_type smallint not null, -- 0: feature, 1: attribute, 2: member, 10: attribute value
  name varchar(127) not null,

  translation varchar(255) not null,
  description varchar(2000),
  link varchar(255),
  wiki varchar(127),
  image varchar(255),
  boolean_yes varchar(255),
  boolean_no varchar(255),
  absent varchar(255),

  primary key (dictionary_id, feature_id)
);

create_table dict_entry_keywords (
  dictionary_id unsigned int not null,
  feature_id unsigned int not null,
  keyword varchar(127) not null,
  primary key (dictionary_id, feature_id, keyword)
);

create table dict_entry_values (
  dictionary_id unsigned int not null,
  feature_id unsigned int not null,
  value varchar(255) not null,
  translation varchar(255) not null,
  primary key (dictionary_id, feature_id, value)
);

create table dict_values (
  dictionary_id unsigned int not null,
  value_id unsigned int not null,
  translation varchar(127) not null,
  primary key (dictionary_id, value_id)
);

create table dict_base_values {
  value_id unsigned int primary key,
  value_type varchar(15) not null,
  key varchar(127) not null
}