5j8du5EJ

1
2DROP TABLE IF EXISTS relationship_info;
3DROP TABLE IF EXISTS brand_synonym_info;
4DROP TABLE IF EXISTS brand_name_info;
5DROP TABLE IF EXISTS brand_country_info;
6DROP TABLE IF EXISTS brand_info;
7DROP TABLE IF EXISTS country_info;
8DROP TABLE IF EXISTS brand_type_info;
9DROP TABLE IF EXISTS locale_info;
10DROP TABLE IF EXISTS relationship_name_info;
11
12CREATE TABLE locale_info (
13id int unsigned AUTO_INCREMENT,
14locale varchar(10) NOT NULL,
15PRIMARY KEY (id),
16UNIQUE(locale)
17);
18
19CREATE TABLE brand_type_info (
20id int unsigned AUTO_INCREMENT,
21brand_type varchar(255) NOT NULL,
22PRIMARY KEY (id),
23UNIQUE(brand_type)
24);
25
26CREATE TABLE country_info (
27id int unsigned AUTO_INCREMENT,
28country_code varchar(10) NOT NULL,
29PRIMARY KEY (id),
30UNIQUE(country_code)
31);
32
33CREATE TABLE relationship_name_info (
34id int unsigned AUTO_INCREMENT,
35relationship_name varchar(50) NOT NULL,
36inverse_relationship_name varchar(50) NOT NULL,
37PRIMARY KEY (id),
38UNIQUE(relationship_name),
39UNIQUE(inverse_relationship_name)
40);
41
42CREATE TABLE brand_info (
43id int unsigned AUTO_INCREMENT,
44brand_dictionary_id INT UNSIGNED NOT NULL,
45brand_type_id INT unsigned NOT NULL,
46status INT UNSIGNED NOT NULL,
47PRIMARY KEY(id),
48INDEX(brand_type_id),
49INDEX(status),
50INDEX(id,status),
51INDEX(status, brand_type_id),
52INDEX(id,brand_type_id,status),
53FOREIGN KEY (brand_type_id) REFERENCES brand_type_info (id)
54);
55
56CREATE TABLE brand_country_info (
57id int unsigned AUTO_INCREMENT,
58brand_id int unsigned NOT NULL,
59country_id int unsigned NOT NULL,
60PRIMARY KEY (id),
61UNIQUE (brand_id,country_id),
62INDEX(brand_id),
63INDEX(country_id),
64FOREIGN KEY (brand_id) REFERENCES brand_info (id),
65FOREIGN KEY (country_id) REFERENCES country_info (id)
66);
67
68CREATE TABLE brand_name_info (
69id int unsigned AUTO_INCREMENT,
70brand_id int unsigned NOT NULL,
71brand_name varchar(255) NOT NULL,
72locale_id int unsigned NOT NULL,
73lcase_brand_name varchar(255) GENERATED ALWAYS AS (lower(brand_name)) VIRTUAL,
74PRIMARY KEY (id),
75UNIQUE (brand_id, locale_id),
76INDEX(locale_id, lcase_brand_name),
77INDEX(brand_id),
78INDEX(locale_id),
79INDEX(lcase_brand_name),
80FOREIGN KEY (brand_id) REFERENCES brand_info (id),
81FOREIGN KEY (locale_id) REFERENCES locale_info (id)
82);
83
84CREATE TABLE brand_synonym_info (
85id int unsigned AUTO_INCREMENT NOT NULL,
86synonym varchar(255) NOT NULL,
87brand_id int unsigned NOT NULL,
88lcase_synonym varchar(255) GENERATED ALWAYS AS (lower(synonym)) VIRTUAL,
89PRIMARY KEY (id),
90UNIQUE(synonym),
91UNIQUE(lcase_synonym),
92INDEX (brand_id),
93FOREIGN KEY (brand_id) REFERENCES brand_info (id)
94);
95
96CREATE TABLE relationship_info (
97id int unsigned AUTO_INCREMENT NOT NULL,
98from_brand int unsigned NOT NULL,
99to_brand int unsigned NOT NULL,
100relationship_id int unsigned NOT NULL,
101relationship_metadata json DEFAULT NULL,
102PRIMARY KEY (id),
103UNIQUE (from_brand, to_brand, relationship_id),
104INDEX(from_brand),
105INDEX(to_brand),
106INDEX(from_brand, to_brand),
107FOREIGN KEY (from_brand) REFERENCES brand_info (id),
108FOREIGN KEY (to_brand) REFERENCES brand_info (id),
109FOREIGN KEY (relationship_id) REFERENCES relationship_name_info (id)
110);