User:Nanobear~enwiki/Regional stats DB


 * CREATE TABLE Region(name VARCHAR(50), type VARCHAR(50), fullname1 VARCHAR(50), fullname2 VARCHAR(50), PRIMARY KEY(name));


 * Example: ("Kaluga", "Oblast", "Kaluga Oblast", "Kaluga Region");
 * The problem here is that different sources write the name in different ways. So perhaps it is useful to save all possible name variations (Russian-language name attribute should also be added.)


 * CREATE TABLE Grp(regionName VARCHAR(50), rubBillions INT, year INT, PRIMARY KEY(regionName, year), FOREIGN KEY(regionName) REFERENCES Region(name));


 * CREATE TABLE Territory(regionName VARCHAR(50), thousandSqKm INT, year INT, PRIMARY KEY(regionName, year), FOREIGN KEY(regionName) REFERENCES Region(name));


 * CREATE TABLE Population(regionName VARCHAR(50), thousandPersons INT, year INT, PRIMARY KEY(regionName, year), FOREIGN KEY(regionName) REFERENCES Region(name));


 * CREATE TABLE AvgEmployed(regionName VARCHAR(50), thousandPersons INT, year INT, PRIMARY KEY(regionName, year), FOREIGN KEY(regionName) REFERENCES Region(name));


 * CREATE TABLE AvgIncomePerCapita(regionName VARCHAR(50), rubles INT, year INT, PRIMARY KEY(regionName, year), FOREIGN KEY(regionName) REFERENCES Region(name));


 * CREATE TABLE AvgExpendPerCapita(regionName VARCHAR(50), rubles INT, year INT, PRIMARY KEY(regionName, year), FOREIGN KEY(regionName) REFERENCES Region(name));


 * CREATE TABLE AvgWages(regionName VARCHAR(50), rubles INT, year INT, PRIMARY KEY(regionName, year), FOREIGN KEY(regionName) REFERENCES Region(name));