Проектирование и разработка БД «Интернет-магазин».

Существует два основных фактора, влияющих на производительность операционной базы данных – объем данных, и количество одновременных обращений к базе данных. Обе этих величины можно предварительно спрогнозировать, но в конечном итоге их оценка напрямую связана с планируемыми масштабами бизнеса. Возможно, если планируется создать очень большой интернет-магазин типа AMAZON, то это потребует создания сложной масштабируемой структуры. Объем базы данных «достаточно большого» интернет-магазина можно оценить следующим образом (данные взяты на основе устных оценок специалистов, основанной на анализе основных электронных торговых площадок России), см. Таблицу 2.Таблица 2. Оценка объема таблиц БДСущностиОценка объема (в строках)Количество поставщиковв пределах 100000Количество товаровв пределах 1000000Количество ежедневных приращений товарных предложенийв пределах 10000Количество заказчиковв пределах 1000000Количество ежедневных заказовв пределах 10000 единицПо устным оценкам специалистов, физический объем такой базы данных будет составлять примерно величину, кратную 100 гигабайт, что немного.В современных системах баз данных используется три основных методах повышения производительности:Индексация таблиц.Секционирование (разбиение таблиц на «части», которые можно обрабатывать по-отдельности и параллельно).Параллельное исполнения запросов (на нескольких ядрах).Нельзя не обратить внимание, что в системе Postgres из перечисленных методов реализовано только поддержка индексов. Точнее говоря, секционирование таблиц и параллельное исполнение в принципе существует, но оно настолько упрощено в сравнении с тем, как это сделано, например, в системе Oracle, что серьезно говорить об использовании секционирования и параллельного исполнения в Postgres нельзя.Индекс – это специализированный объект базы данных, используемый при доступе к строкам таблиц. Индексы обычно хранятся в тех же схемах, что и таблицы, с которыми они ассоциируются. Индексы бывают нескольких типов, с точки зрения их структуры; наиболее используемый тип индексов – B+-индексы.Для создания индексов в Postgres используется команда CREATETABLE (примеры приведены ниже).При построении индексов для проектируемой базы данных были использованы следующие рекомендации:Суррогатные идентификаторы записей должны иметь индекс. В системе Postgres по полям первичного ключа автоматически строится уникальный индекс, поэтому строить такой индекс повторно не следует (но это возможно).Поля, на которые наложены ограничения референциальной целостности, обычно должны индексироваться.Некоторые категориальные поля типа «дата заказа», «номер сессии», которые хотя и не имеют явно наложенного ограниченияреференциальной целостности, но используются аналогичным способом.Следует ли индексировать поля типа «название товара», является неоднозначным, поскольку тесно связан с тем, каким именно образом реализуется поиск со стороны приложения. Практика показывает, что поиск записей по критериям непосредственно в базе данных может оказаться неудовлетворительным с точки зрения эффективности, поэтому приложения могут использовать какие-то специальные средства поиска. Следовательно, вопрос об индексации других полей (не попадающих в критерии, перечисленные выше) нуждается в дополнительном изучении.В качестве примера приведем примеры индексов для таблицы web_store.product_offer:CREATE INDEX i__product_offer__product_idON web_store.product_offer USING btree (product_id);CREATE INDEX i__product_offer__supplier_idON web_store.product_offer USING btree (supplier_id);Позднее, на основе анализа тестовой или промышленной эксплуатации БД можно будет выявить «дорогие» с точки зрения затрат ресурсов запросы и построить дополнительные индексы.Для тестирования работы разработанной схемы было реализовано макетное приложение при помощи инструментальной среды VisualStudio и языка программирования C#.Для реализации приложения были использованы технологий WindowsFoundationPresentation (WFP), предназначенная для разработки оконных приложений, иEntityFramework, предназначенная для обеспечения доступа к данными БД их приложений C#.Ниже приведены схема базы данных для EntityFramework, и скриншоты окон, содержащих данные БД.Рисунок 3. EntityFramework-диаграммаРисунок 4. Оконная форма «Заказчик»Рисунок 5. Оконная форма «Заказ»ЗаключениеВ курсовой работе проведено научно-прикладное исследование, целью которой является создание базы данных для хранение информации интернет-магазина.При выполнении работы было оценена актуальность и перспективность работ в данном направлении при помощи анализа современного состояние дел в сфере электронных средств торговли. Так же автор провел визуальный анализ существующих в настоящее время интернет-магазинов и торговых площадок. На основе этого анализа были выявлены бизнес-процессы, реализующиеся в сфере электронной торговли. Так же проведено исследование по выявлению структур данных, которые необходимы для создания схемы базы данных. Результаты этого этапа оформлены в виде логической схемы проектируемой базы данных в виде диаграммы «сущность-связь». Для создания физической схемы был выполнено сравнение существующих на рынке систем баз данных, из которых была выбрана система Postgres как наиболее удовлетворяющая заявленным целям проекта. Для этой целевой системы на основании логической схемы разработана физическая схемы. Она содержит все необходимые SQL-команды для исполнения в реальном сервере баз данных.Кроме того, исследован вопрос об эффективном функционировании созданной базы данных, рассмотрены общие вопросы производительности баз данных, и предложены базовые методы повышения производительности, основанные на создании индексов.Результатом выполнения работы является разработанная схемы базы данных, которая может быть использована в реальных системах электронной торговли. Цели курсовой работы выполнены.Используемые источникиАрхитектура торговых площадок //Сайт https://habr.com/ ru/post/530090/ (дата обращения 04.04.2022).Общероссийские классификаторы // Сайт https://classifikators.ru/ (дата обращения: 04.04.2022).Марк Прайс: C# 7 и .NET Core. Кросс-платформенная разработка для профессионалов//Прайс Марк. Пер. с англ. – Сп-Б. изд. Питер, 2018. – 640 с.Домбровская, Бейликова, Новиков: Оптимизация запросов PostgreSQL/ Домбровская и др. – М., ДМК-Пресс, 2022. – 278 с.Дмитрий Осипов: Технологии проектирования баз данных// Осипов Д. - М., ДМК-Пресс, 2019. – 498 с.Евгений Моргунов: PostgreSQL. Основы языка SQL. Учебное пособие// Моргунов Е. – М., изд. BHV, 2018. – 336 с.Описание формата данных JSON//Сайт https://www.json.org/ (дата обращения 04.04.2022).ПриложениеСкрипт для создания схемы базы данных на языке PostgreSQLCREATE SCHEMA web_store;ALTER SCHEMA web_store OWNER TO postgres;CREATE TABLE web_store.supplier(idbigserial not null, "name" character varying(400),inn character varying(50) not null,kpp character varying(50),ogrn character varying(50),region char(2) not null,postal_address character varying(4000),factual_address character varying(4000),email character varying(300),phone character varying(300),additional_infojsonb);ALTER TABLE web_store.supplier ADD CONSTRAINT pk_supplier PRIMARY KEY (id);ALTER TABLE web_store.supplier OWNER TO postgres;CREATE TABLE web_store.product(idbigserial not null,name character varying(400) not null,description text,original_name character varying(400),manufacturer character varying(400), sku character varying(50),okved2_code character varying(50),okpd2_code character varying(50),okei_code character varying(50),additional_infojsonb);ALTER TABLE web_store.product ADD CONSTRAINT pk_product PRIMARY KEY (id);ALTER TABLE web_store.product OWNER TO postgres;CREATE TABLE web_store.category (idbigserial not null,category_code character varying(50) not null,category_description character varying(400),parent_code character varying(50),parent_code_idbigint); ALTER TABLE web_store.categoryADD CONSTRAINT pk_category PRIMARY KEY (id);ALTER TABLE web_store.categoryADD CONSTRAINT fk__category__parent_code_id FOREIGN KEY (parent_code_id) REFERENCES web_store.category (id);CREATE INDEX i__category__parent_code_id ON web_store.category USING btree (parent_code_id);ALTER TABLE web_store.category OWNER TO postgres;CREATE TABLE web_store.product_to_category (idbigserial not null,category_idbigint not null,product_idbigint not null); ALTER TABLE web_store.product_to_category ADD CONSTRAINT pk_product_to_category PRIMARY KEY (id);ALTER TABLE web_store.product_to_categoryADD CONSTRAINT fk__product_to_category__category_id FOREIGN KEY (category_id) REFERENCES web_store.category (id);ALTER TABLE web_store.product_to_categoryADD CONSTRAINT fk__product_to_category__product_id FOREIGN KEY (product_id) REFERENCES web_store.product (id);CREATE INDEX i__product_to_category__category_id ON web_store.product_to_category USING btree (category_id);CREATE INDEX i__product_to_category__product_id ON web_store.product_to_category USING btree (product_id);ALTER TABLE web_store.product_to_category OWNER TO postgres;CREATE TABLE web_store.product_offer(idbigserial not null,product_idbigint not null,supplier_idbigint not null,price numeric(20,2) not null,vatint,avialable_quantity numeric(20,2) null,avialable_from timestamp not null,avialable_till timestamp null,additional_infojsonb);ALTER TABLE web_store.product_offer ADD CONSTRAINT pk_product_offer PRIMARY KEY (id);ALTER TABLE web_store.product_offerADD CONSTRAINT fk__product_offer__product_id FOREIGN KEY (product_id) REFERENCES web_store.product (id);ALTER TABLE web_store.product_offerADD CONSTRAINT fk__product_offer__supplier_id FOREIGN KEY (supplier_id) REFERENCES web_store.supplier (id);CREATE INDEX i__product_offer__product_id ON web_store.product_offer USING btree (product_id);CREATE INDEX i__product_offer__supplier_id ON web_store.product_offer USING btree (supplier_id);ALTER TABLE web_store.product_offer OWNER TO postgres;CREATE TABLE web_store.product_offer_condition(idbigserial not null,product_offer_idbigint not null,region char(2) not null,delivery character varying(50) not null,delivery_description character varying(500),delivery_price numeric(20,2) not null,min_quantity numeric(20,2),delivery_timeint,additional_infojsonb);ALTER TABLE web_store.product_offer_conditionADD CONSTRAINT pk_product_offer_condition PRIMARY KEY (id);ALTER TABLE web_store.product_offer_conditionADD CONSTRAINT fk__product_offer__product_offer_id FOREIGN KEY (product_offer_id) REFERENCES web_store.product_offer (id);CREATE INDEX i__product_offer_condition__product_offer_id ON web_store.product_offer_condition USING btree (product_offer_id); ALTER TABLE web_store.product_offer_condition OWNER TO postgres;CREATE TABLE web_store.product_picture (idbigserial not null,url character varying(400) not null,mimetype character varying(50),ordinalint not null,product_idbigint,product_offer_idbigint);ALTER TABLE web_store.product_picture ADD CONSTRAINT pk_product_picture PRIMARY KEY (id);ALTER TABLE web_store.product_pictureADD CONSTRAINT fk__product_picture__product_id FOREIGN KEY (product_id) REFERENCES web_store.product (id);ALTER TABLE web_store.product_pictureADD CONSTRAINT fk__product_picture__product_offer_id FOREIGN KEY (product_offer_id) REFERENCES web_store.product_offer (id);CREATE INDEX i__product_picture__product_id ON web_store.product_picture USING btree (product_id);CREATE INDEX i__product_picture__product_offer_id ON web_store.product_picture USING btree (product_offer_id);ALTER TABLE web_store.product_picture OWNER TO postgres;CREATE TABLE web_store.customer(idbigserial not null, "name" character varying(400),inn character varying(50),kpp character varying(50),ogrn character varying(50),region char(2) not null,postal_address character varying(4000),factual_address character varying(4000),email character varying(300),phone character varying(300),additional_infojsonb);ALTER TABLE web_store.customer ADD CONSTRAINT pk_customer PRIMARY KEY (id);ALTER TABLE web_store.customer OWNER TO postgres;CREATE TABLE web_store.contact_person(idbigserial not null,supplier_idbigint null,customer_idbigint null,fio character varying(400),position character varying(400),phone character varying(300),fax character varying(300),email character varying(300));ALTER TABLE web_store.contact_personADD CONSTRAINT fk__contact_person__supplier_id FOREIGN KEY (supplier_id) REFERENCES web_store.supplier (id);ALTER TABLE web_store.contact_personADD CONSTRAINT fk__contact_person__customer_id FOREIGN KEY (customer_id) REFERENCES web_store.customer (id);CREATE INDEX i__contact_person__supplier_id ON web_store.contact_person USING btree (supplier_id);CREATE INDEX i__contact_person__customer_id ON web_store.contact_person USING btree (customer_id);CREATE TABLE web_store.product_order(idbigserial not null,customer_idbigint not null,total_price numeric(20,2) not null, placed_date timestamp not null,statusint);ALTER TABLE web_store.product_order ADD CONSTRAINT pk_product_order PRIMARY KEY (id);ALTER TABLE web_store.product_orderADD CONSTRAINT fk__product_order__customer_id FOREIGN KEY (customer_id) REFERENCES web_store.customer (id);CREATE INDEX i__product_order__customer_id ON web_store.product_order USING btree (customer_id);ALTER TABLE web_store.contact_person OWNER TO postgres;CREATE TABLE web_store.product_order_item(idbigserial not null,product_orderbigint not null,product_offer_condition_idbigint not null,quantity numeric(20,2) null);ALTER TABLE web_store.product_order_item ADD CONSTRAINT pk_product_order_item PRIMARY KEY (id);ALTER TABLE web_store.product_order_itemADD CONSTRAINT fk__product_order_item__product_order_item_id FOREIGN KEY (product_offer_condition_id) REFERENCES web_store.product_order(id);CREATE INDEX i__product_order_item__product_offer_condition_id ON web_store.product_order_item USING btree (product_offer_condition_id);ALTER TABLE web_store.product_order_item OWNER TO postgres;Приложение. Скрипт для заполнения БД тестовыми даннымиinsert into dbo.supplier ( "name", inn, kpp, ogrn, region, postal_address, factual_address, email, phone )values ('IP Stepanov', '772343455601',null,null,'77','Moscow, Gagarinastr, 1', null, 'step@mail.ru', null), ('JJ Snigir', '7723434556','770000001',null,'77','Moscow, Leninastr, 2', null, 'ooosnigir@mail.ru', null);insert into dbo.product( [name], original_name, manufacturer, sku, okved2_code, okpd2_code, okei_code)values( 'Fish' , 'Nice fish', null, 'РБ-0203', '01.63', null, null ),( 'Corn', 'Agricultural corn', null, 'РМ-9991', '06.01', null, null );insert into dbo.product_offer( product_id, supplier_id, price, vat, avialable_quantity, avialable_from )values (7,1, 500, 10, 1000, '2022-01-01'), (8,1, 650, 10, 2000, '2022-03-01'), (9,2, 1050, 20, 25000, '2021-03-01'), (7,2, 1150, 25, 35000, '2021-08-01');insert into dbo.customer ( "name", region, factual_address, email )values ('Lena','77','Moscow, Novaya str, 1', 'oleg2000@mail.ru'), ('Andrew', '77','Moscow, Gagarinastr, 2', 'cp@gmail.ru'), ('Elen', '23','Krasnodar, Leninastr 2', 'lenusik@mail.ru');insert into dbo.product_order( customer_id, placed_date, total_price, status )values (1, Getdate(), 345.45, 1), (2, Getdate(), 8752.0, 1);