Проектирование базы данных для хранения различной информации метаданных
Поэтому я пытаюсь создать базу данных, которая позволит мне связать один продукт с несколькими категориями. Эту часть я понял. Но что я не могу решить, так это проблему хранения информации о различных типах продуктов.
Например, продукт может быть книгой (в этом случае мне понадобятся метаданные, относящиеся к этой книге, такие как isbn, автор и т. Д.), Или это может быть бизнес-листинг (имеющий разные метаданные).
Как я должен заняться этим?
5 ответов
Это называется Наблюдением.
Три объекта, например
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
РЕДАКТИРОВАТЬ:
Джеффри поднял правильную точку зрения (см. Комментарий), поэтому я расширю ответ.
Модель позволяет динамически (на лету) создавать любое количество объектов с любым типом свойств без изменения схемы. Однако, эта гибкость имеет цену - хранение и поиск медленнее и сложнее, чем в обычной конструкции стола.
Пришло время для примера, но сначала, чтобы упростить ситуацию, я сведу модель к виду.
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'
;
Я не собирался отвечать, но сейчас у принятого ответа очень плохая идея. Реляционная база данных никогда не должна использоваться для хранения простых пар атрибут-значение. Это вызовет много проблем в будущем.
Лучший способ справиться с этим - создать отдельную таблицу для каждого типа.
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.
Каждая строка каждой таблицы должна представлять суждение о реальном мире, а структура таблиц и их ограничения должны отражать представляемые реальности. Чем ближе вы можете подойти к этому идеалу, тем чище будут данные, и тем проще будет составлять отчеты и расширять систему другими способами. Это также будет работать более эффективно.
Вы могли бы пойти с подходом без схемы:
Храните метаданные в столбце TEXT как объект JSON (или другую сериализацию, но JSON лучше по причинам, которые скоро объяснят).
Преимущества этой техники:
Меньше запросов: вы получаете всю информацию в одном запросе, нет необходимости в "направленных" запросах (для получения мета-метаданных) и объединений.
Вы можете добавлять / удалять любые атрибуты, которые хотите, в любое время, без необходимости изменять таблицу (что проблематично в некоторых базах данных, например, Mysql блокирует таблицу, и это занимает много времени с огромными таблицами)
Поскольку это JSON, вам не нужна дополнительная обработка на вашем бэкэнде. Ваша веб-страница (я предполагаю, что это веб-приложение) просто читает JSON как есть из вашего веб-сервиса, и все, вы можете использовать объект JSON с javascript так, как вам нравится.
Проблемы:
Потенциально потерянное пространство, если у вас есть 100 книг с одним автором, таблица автора со всеми книгами, имеющими только author_id, является более экономичной с точки зрения пространства.
Нужно реализовать индексы. поскольку ваши метаданные являются объектом JSON, у вас нет индексов сразу. Но довольно легко реализовать конкретный индекс для конкретных метаданных, которые вам нужны. Например, вы хотите индексировать по автору, поэтому вы создаете таблицу author_idx с author_id и item_id, когда кто-то ищет автора, вы можете посмотреть эту таблицу и сами элементы.
В зависимости от масштаба это может быть излишним. в меньших масштабах соединения будут работать просто отлично.
Продукт должен быть напечатан. например, включить type_id в таблицу продуктов, которая указывает на категории продуктов, которые вы будете поддерживать, и позволит вам узнать, какие другие таблицы нужно запрашивать для соответствующих связанных атрибутов.
В такой проблеме у вас есть три варианта:
- Создайте таблицу с "общими" столбцами. Например, если вы продаете и книги, и тостеры, вполне вероятно, что у ваших тостеров нет ISBN и названия, но у них все еще есть некоторый идентификатор продукта и описание. Поэтому дайте полям общие имена, такие как "product_id" и "description", а для книг product_id - это ISBN, для тостеров - номер детали производителя и т. Д.
Это работает, когда объекты реального мира обрабатываются одинаково, по крайней мере, по большей части, и поэтому должны иметь, если не "одинаковые" данные, по крайней мере аналогичные данные. Это ломается, когда есть реальные функциональные различия. Например, если для тостеров мы рассчитываем ватт = вольт * ампер, вполне вероятно, что для книг нет соответствующего расчета. Когда вы начинаете создавать поля pages_volts, которые содержат количество страниц для книг и напряжение для тостеров, все вышло из-под контроля.
Используйте схему собственности / стоимости, как предлагает Дамир. Смотрите мой комментарий к его сообщению для плюсов и минусов там.
Что я обычно предлагаю, так это схему типа / подтипа. Создайте таблицу для "product", которая содержит код типа и общие поля. Затем для каждого из истинных типов - книг, тостеров, кошек и т. Д. - создайте отдельную таблицу, которая связана с таблицей продуктов. Затем, когда вам нужно выполнить специфичную для книги обработку, обработайте таблицу книг. Когда вам нужно сделать общую обработку, обработайте таблицу продуктов.
Я понимаю, что это может быть не тот ответ, который вы ищете, однако, к сожалению, реляционная база данных ( SQL) построена на идее структурированной предопределенной схемы. Вы пытаетесь сохранить неструктурированные данные без схемы в модели, которая не была создана для этого. Да, вы можете подделать его, чтобы технически хранить бесконечное количество метаданных, однако это скоро вызовет множество проблем и быстро выйдет из-под контроля. Просто посмотрите на Wordpress и на количество проблем, которые у них возникли с этим подходом, и вы легко поймете, почему это не очень хорошая идея.
К счастью, это давняя проблема с реляционными базами данных, поэтому были разработаны базы данных без схемы NoSQL, использующие документный подход, и за последнее десятилетие они стали очень популярными. Это то, что все технологические компании из списка Fortune 500 используют для хранения постоянно изменяющихся пользовательских данных, поскольку это позволяет отдельным записям иметь столько или меньше полей (столбцов), сколько они пожелают, оставаясь при этом в одной коллекции (таблице).
Поэтому я бы предложил изучить базы данных NoSQL, такие как MongoDB, и попытаться либо преобразовать в них, либо использовать их вместе с вашей реляционной базой данных. Любые типы данных, которые, как вы знаете, должны иметь одинаковое количество представляющих их столбцов, должны храниться в SQL, а любые типы данных, которые, как вы знаете, будут различаться между записями, должны храниться в базе данных NoSQL.