Таким образом, я пытаюсь разработать базу данных, которая позволит мне соединять один продукт с несколькими категориями. Эта первая часть фигурировала. Но то, что я не могу разрешить, является проблемой содержания другого типа сведений о продукте.
Например, продуктом могла быть книга (в этом случае, мне будут нужны метаданные, которые обращаются к той книге как ISBN, автор и т.д.), или это мог быть список бизнеса (который имеет различные метаданные)..
Как я должен заняться этим?
Это называется паттерном наблюдения.
Три объекта, для примера
Book
Title = 'Gone with the Wind'
Author = 'Margaret Mitchell'
ISBN = '978-1416548898'
Cat
Name = 'Phoebe'
Color = 'Gray'
TailLength = 9 'inch'
Beer Bottle
Volume = 500 'ml'
Color = 'Green'
Вот как могут выглядеть таблицы:
Entity
EntityID Name Description
1 'Book' 'To read'
2 'Cat' 'Fury cat'
3 'Beer Bottle' 'To ship beer in'
PropertyType
PropertyTypeID Name IsTrait Description
1 'Height' 'NO' 'For anything that has height'
2 'Width' 'NO' 'For anything that has width'
3 'Volume' 'NO' 'For things that can have volume'
4 'Title' 'YES' 'Some stuff has title'
5 'Author' 'YES' 'Things can be authored'
6 'Color' 'YES' 'Color of things'
7 'ISBN' 'YES' 'Books would need this'
8 'TailLength' 'NO' 'For stuff that has long tails'
9 'Name' 'YES' 'Name of things'
.
Property
PropertyID EntityID PropertyTypeID
1 1 4 -- book, title
2 1 5 -- book, author
3 1 7 -- book, isbn
4 2 9 -- cat, name
5 2 6 -- cat, color
6 2 8 -- cat, tail length
7 3 3 -- beer bottle, volume
8 3 6 -- beer bottle, color
.
Measurement
PropertyID Unit Value
6 'inch' 9 -- cat, tail length
7 'ml' 500 -- beer bottle, volume
.
Trait
PropertyID Value
1 'Gone with the Wind' -- book, title
2 'Margaret Mitchell' -- book, author
3 '978-1416548898' -- book, isbn
4 'Phoebe' -- cat, name
5 'Gray' -- cat, color
8 'Green' -- beer bottle, color
EDIT:
Джеффри поднял важный вопрос (см. комментарий), поэтому я расширю ответ.
Модель позволяет динамически (на лету) создавать любое количество сущностей с любым типом свойств без изменения схемы. Однако, за эту гибкость приходится платить - хранение и поиск данных происходит медленнее и сложнее, чем в обычном табличном дизайне.
Пришло время для примера, но сначала, чтобы упростить ситуацию, я сплющу модель в представление.
create view vModel as
select
e.EntityId
, x.Name as PropertyName
, m.Value as MeasurementValue
, m.Unit
, t.Value as TraitValue
from Entity as e
join Property as p on p.EntityID = p.EntityID
join PropertyType as x on x.PropertyTypeId = p.PropertyTypeId
left join Measurement as m on m.PropertyId = p.PropertyId
left join Trait as t on t.PropertyId = p.PropertyId
;
Используя пример Джеффри из комментария
with
q_00 as ( -- all books
select EntityID
from vModel
where PropertyName = 'object type'
and TraitValue = 'book'
),
q_01 as ( -- all US books
select EntityID
from vModel as a
join q_00 as b on b.EntityID = a.EntityID
where PropertyName = 'publisher country'
and TraitValue = 'US'
),
q_02 as ( -- all US books published in 2008
select EntityID
from vModel as a
join q_01 as b on b.EntityID = a.EntityID
where PropertyName = 'year published'
and MeasurementValue = 2008
),
q_03 as ( -- all US books published in 2008 not discontinued
select EntityID
from vModel as a
join q_02 as b on b.EntityID = a.EntityID
where PropertyName = 'is discontinued'
and TraitValue = 'no'
),
q_04 as ( -- all US books published in 2008 not discontinued that cost less than $50
select EntityID
from vModel as a
join q_03 as b on b.EntityID = a.EntityID
where PropertyName = 'price'
and MeasurementValue < 50
and MeasurementUnit = 'USD'
)
select
EntityID
, max(case PropertyName when 'title' than TraitValue else null end) as Title
, max(case PropertyName when 'ISBN' than TraitValue else null end) as ISBN
from vModel as a
join q_04 as b on b.EntityID = a.EntityID
group by EntityID ;
Это выглядит сложным для написания, но при ближайшем рассмотрении вы можете заметить закономерность в CTE.
Теперь предположим, что у нас есть стандартная фиксированная схема, где каждое свойство объекта имеет свой столбец. Запрос будет выглядеть примерно так:
select EntityID, Title, ISBN
from vModel
WHERE ObjectType = 'book'
and PublisherCountry = 'US'
and YearPublished = 2008
and IsDiscontinued = 'no'
and Price < 50
and Currency = 'USD'
;
Товар должен быть типизированным. например включить type_id в таблицу продуктов, которая указывает на категории продуктов, которые вы будете поддерживать, и позволяет узнать, в каких других таблицах следует запрашивать соответствующие связанные атрибуты.
Вы можете использовать подход без схемы:
Храните метаданные в столбце TEXT как объект JSON (или другую сериализацию, но JSON лучше по причинам, которые вскоре будут объяснены).
Преимущества этого метода:
Меньше запросов: вы получаете всю информацию в одном запросе, нет необходимости в «направленных» запросах (для получения мета-метаданных) и объединениях.
Вы можете добавлять / удалять любые атрибуты в любое время, нет необходимости изменять таблицу (что проблематично в некоторых базах данных, например, Mysql блокирует таблицу, и это занимает много времени с огромными таблицами)
Поскольку это JSON , вам не нужна дополнительная обработка на вашем сервере. Ваша веб-страница (я предполагаю, что это веб-приложение) просто считывает JSON как есть из вашей веб-службы, и все, вы можете использовать объект JSON с javascript, как хотите.
Проблемы:
Потенциально потраченное впустую пространство, если у вас есть 100 книг с одним и тем же автором, таблица авторов со всеми книгами, имеющими только author_id, более экономична.
Необходимо внедрить индексы. поскольку ваши метаданные представляют собой объект JSON, у вас сразу нет индексов. Но довольно легко реализовать конкретный индекс для конкретных метаданных, которые вам нужны. Например, вы хотите индексировать по автору, поэтому вы создаете таблицу author_idx с author_id и item_id, когда кто-то ищет автора, вы можете искать эту таблицу и сами элементы.
В зависимости от масштаба это могло быть излишним. в меньшем масштабе соединения будут работать нормально.
Я не собирался отвечать, но сейчас у принятого ответа очень плохая идея. Реляционная база данных никогда не должна использоваться для хранения простых пар атрибут-значение. Это вызовет множество проблем в будущем.
Лучший способ справиться с этим - создать отдельную таблицу для каждого типа.
Product
-------
ProductId
Description
Price
(other attributes common to all products)
Book
----
ProductId (foreign key to Product.ProductId)
ISBN
Author
(other attributes related to books)
Electronics
-----------
ProductId (foreign key to Product.ProductId)
BatteriesRequired
etc.
Каждая строка каждой таблицы должна представлять предложение о реальном мире, а структура таблиц и их ограничения должны отражать реалии, которые представляются. Чем ближе вы приблизитесь к этому идеалу, тем чище будут данные, тем проще будет составлять отчеты и расширять систему другими способами. Он также будет работать более эффективно.
В этой задаче у вас есть три варианта:
Это работает, когда все объекты реального мира обрабатываются в таким же образом, по крайней мере, по большей части, и поэтому должны иметь если не «одинаковые» данные, то хотя бы аналогичные данные. Это не работает, когда есть реальные функциональные различия. Например, если для тостеров мы вычисляем ватт = вольт * ампер, вполне вероятно, что для книг нет соответствующего расчета. Когда вы начинаете создавать поля pages_volts, которые содержат количество страниц для книг и напряжение для тостеров, все вышло из-под контроля.
Используйте схему свойство / значение, как предлагает Дамир. См. Мой комментарий к его посту, чтобы узнать о плюсах и минусах.
Я обычно предлагаю схему типа / подтипа. Создайте таблицу для «продукта», содержащую код типа и общие поля. Затем для каждого из истинных типов - книг, тостеров, кошек и т. Д. - создайте отдельную таблицу, которая будет связана с таблицей товаров. Затем, когда вам нужно выполнить обработку для конкретной книги, обработайте таблицу книги.Когда вам нужно выполнить общую обработку, обработайте таблицу продуктов.