проектирование реляционных баз данных — нормализация и отношения
Я новичок в разработке баз данных, и я не уверен, правильно ли я реализовал нормализацию и отношения в дизайне базы данных для моего приложения.
Разрабатываемое приложение работает со списками покупок и продуктами и будет использовать базу данных следующим образом:
- Пользователи заходят через фейсбук на сайт.
- Существует большая таблица с миллионами «кодов продуктов». Каждый код продукта представляет отдельный продукт (например, Apple, телевизор, мобильный телефон, зубная щетка и т. Д.).
- Пользователи могут создавать различные списки покупок, и каждый список покупок будет содержать один или несколько «кодов продуктов».
я имею «Попытка» создать две разные конструкции для базы данных в попытке определить, какой из них может быть лучше для сценария. Один использует отношение один ко многим и одно отношение многие ко многим, оба из которых связывают список покупок в товар:
ВОПРОСЫ:
-
Относительно одного ко многим: Учитывая, что будут миллионы разных идентификаторов продукта, не один ко многим дизайн производит много избыточной информации в assigned_products Таблица? Таблица продуктов будет иметь поля, похожие на:
________________________________________________ |assigned_p_id | shopping_list_id | product_code | |--------------+------------------+--------------| | 1 | 1 | aa11aab | |--------------+------------------+--------------| | 2 | 1 | zz1bbbb | |--------------+------------------+--------------| | 3 | 2 | aa11aab |
как вы видете, aa11aab был повторен дважды, потому что кто-то еще создал список покупок с тем же предметом, поэтому мне интересно, не создаст ли это избыточность проблемы с базой данных с количеством ненужного места, которое займет каждый повторяющийся код продукта?
-
Относительно многих ко многим: Диаграмма, кажется, убирает избыточность, имея таблицу shopping_list_products хранить значения идентификатора, а не повторять код продукта, ОДНАКО, это приведет к снижению производительности при получении информации? Я предполагаю, что несколько присоединяется должно происходить между таблицами, чтобы каждый отдельный пользователь получал все свои списки покупок и коды продуктов в этих списках покупок.
-
Вообще говоря: Могут ли быть какие-либо ошибки в моих отношениях / нормализации? (Я новичок в этой области)
Решение
Вот несколько советов, с чего начать.
Вам понадобится таблица для хранения информации о покупках. Из данных видно, что Wile E Coyote приобрел Dyno-кошелек и фортепиано …
CREATE TABLE IF NOT EXISTS `purchase` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`user_id` int unsigned NOT NULL,
`item_id` int unsigned NOT NULL,
`quantity` double DEFAULT 1,
`price` double NOT NULL,
`qwhen` datetime NOT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (`user_id`) REFERENCES user(id) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (`product_id`) REFERENCES product(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB Comment='Purchases';
purchase data
+----+---------+------------+----------+--------+---------------------+
| id | user_id | product_id | quantity | price | created |
+----+---------+------------+----------+--------+---------------------+
| 1 | 1 | 1 | 1 | 100.00 | 2016-07-02 20:42:13 |
| 1 | 1 | 3 | 1 | 734.23 | 2016-07-02 21:23:40 |
| .. | ....... | .......... | ........ | ...... | ................... |
+----+---------+------------+----------+--------+---------------------+
Вам также понадобится стол для хранения информации о товарах. Я предполагаю, что вы продаете физические продукты, поэтому я добавил ean, upca и некоторые другие столбцы, которые могут быть полезны.
CREATE TABLE IF NOT EXISTS `product` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`manufacturer_id` int unsigned NOT NULL,
`ean` int unsigned NOT NULL,
`upca` int unsigned NOT NULL,
`upce` int unsigned NOT NULL,
`name` varchar(100) NOT NULL,
`height` double DEFAULT NULL,
`width` double DEFAULT NULL,
`depth` double DEFAULT NULL,
`weight` double DEFAULT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (`manufacturer_id`) REFERENCES manufacturer(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB Comment='Item master file';
product data
+----+-----------------+---------------+--------------+--------+------------+--------+-------+-------+--------+
| id | manufacturer_id | ean | upca | upce | name | height | width | depth | weight |
+----+-----------------+---------------+--------------+--------+------------+--------+-------+-------+--------+
| 1 | 1 | 1234567890123 | 123456789012 | 123456 | Dyno-might | 12.0 | 1.0 | 1.0 | 0.5 |
| 2 | 1 | 2345678901234 | 234567890123 | 234567 | UltraLaser | 96.0 | 96.0 | 96.0 | 450.0 |
| 3 | 2 | 3456789012345 | 345678901234 | 345678 | Piano | 36.0 | 60.0 | 72.0 | 375.0 |
| .. | ............... | ............. | ............ | ...... | .......... | ...... | ..... | ..... | ...... |
+----+-----------------+---------------+--------------+--------+------------+--------+-------+-------+--------+
Когда мы продаем вещи людям, приятно обращаться к ним формально, поэтому у нас должна быть таблица действительных приветствий. user таблица будет иметь внешний ключ к этой таблице.
CREATE TABLE IF NOT EXISTS `salutation` (
`id` varchar(5) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB Comment='Salutations';
salutation data
+------+
| id |
+------+
| Mr. |
| Mrs. |
| Miss |
| Ms. |
| Dr. |
| Rev. |
| .... |
+------+
Суффиксы имен являются частью имени человека, поэтому мы сделаем эту часть user таблицу и предоставить внешний ключ к этой таблице.
CREATE TABLE IF NOT EXISTS `suffix` (
`id` varchar(5) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB Comment='Suffixes';
suffix data
+-----+
| id |
+-----+
| Sr. |
| Jr. |
| II |
| III |
| IV |
| ... |
+-----+
И, конечно, у нас есть пользователи, хотя было бы целесообразнее называть их клиентами.
CREATE TABLE IF NOT EXISTS `user` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`facebook_id` int unsigned NOT NULL,
`email` varchar(255) NOT NULL,
`salutation_id` varchar(5) NOT NULL,
`first_name` varchar(50) NOT NULL,
`middle_name` varchar(50) DEFAULT NULL,
`last_name` varchar(50) NOT NULL,
`suffix_id` varchar(5) DEFAULT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (`salutation_id`) REFERENCES salutation(id) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (`suffix_id`) REFERENCES suffix(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB Comment='Users';
user data
+----+-------------+------------------------+---------------+------------+-------------+-----------+-----------+
| id | facebook_id | email | salutation_id | first_name | middle_name | last_name | suffix_id |
+----+-------------+------------------------+---------------+------------+-------------+-----------+-----------+
| 1 | 123456789 | [email protected] | Mr. | Wile | E | Coyote | NULL |
| .. | ........... | ...................... | ............. | .......... | ........... | ......... | ......... |
+----+-------------+------------------------+---------------+------------+-------------+-----------+-----------+
Другие решения
По сути, вы делаете одно и то же в обоих случаях. В первом случае вы отображаете shopping_list_id в product_codeво втором случае вы отображаете его product_id, Используете ли вы product_code или же product_id похоже, что они оба являются внешними ключами, представляющими product юридическое лицо. В обоих случаях, чтобы попасть в ряд продуктов, вы должны присоединиться к products таблица с использованием этого поля.
Как правило, вы должны сопоставить два id столбцы (ваш второй вариант), так что в обоих случаях объединения выполняются с использованием целочисленных первичных ключей. Это не только повышает эффективность хранения и индексации, но и ускоряет эти объединения.
Кроме того, как указано в одном из комментариев, вы должны добавить первичный ключ с автоинкрементом к shopping_list_products Таблица.
Кстати, для соглашения об именовании вы можете рассмотреть возможность именования первичных ключей с помощью id, Например, вместо того, чтобы иметь products.product_idвы бы products.id и так далее для каждого стола.