Схема динамической базы данных

У нас сработало строго типизированное поле xml в MSSQL.

Вся суть реляционной БД в том, чтобы ваши данные были в безопасности и согласованы. В тот момент, когда вы позволяете пользователям изменять схему, наступает целостность ваших данных ...

Если вам нужно хранить разнородные данные, например, как сценарий CMS, я бы предложил хранить XML, проверенный XSD, в строке. Конечно, вы теряете производительность и возможности удобного поиска, но ИМХО это хороший компромисс.

С 2016 года забудьте про XML! Используйте JSON для хранения пакета нереляционных данных с соответствующим образом типизированным столбцом в качестве бэкэнда. Обычно вам не нужно запрашивать по значению внутри сумки, что будет медленным, хотя многие современные базы данных SQL изначально понимают JSON.

Мне кажется, что на самом деле вам нужна некая «мета-схема», схема базы данных, способная описывать гибкую схему для хранения фактических данных. Изменения динамической схемы являются деликатным делом, и это не то, с чем вы хотите связываться, особенно если пользователям разрешено вносить изменения.

Вы не найдете базы данных, более подходящей для этой задачи, чем любая другая, поэтому лучше всего выбрать ее на основе других критериев. Например, какую платформу вы используете для размещения БД? На каком языке написано приложение? так далее

Чтобы прояснить, что я имею в виду под «мета-схемой»:

CREATE TABLE data (
    id INTEGER NOT NULL AUTO_INCREMENT,
    key VARCHAR(255),
    data TEXT,

    PRIMARY KEY (id)
);

Это очень простой пример, у вас, вероятно, будет что-то более специфичное для ваших нужд (и, надеюсь, с ним немного легче работать), но он действительно служит для иллюстрации моей точки зрения. Вы должны рассматривать саму схему базы данных как неизменную на уровне приложения; любые структурные изменения должны быть отражены в данных (то есть в экземпляре этой схемы).

Раньше я выбирал вариант C - Создание «длинной, узкой» таблицы, в которой значения динамических столбцов хранятся в виде строк, которые затем необходимо повернуть, чтобы создать «короткий, широкий» набор строк, содержащий все значения для определенного объекта.. Однако я использовал ORM, и это ДЕЙСТВИТЕЛЬНО делало вещи болезненными. Я не могу представить, как бы вы это сделали, скажем, в LinqToSql. Думаю, мне придется создать хеш-таблицу для ссылки на поля.

@Skliwz: Я предполагаю, что он больше заинтересован в том, чтобы позволить пользователям создавать определяемые пользователем поля.

Я знаю, что указанные в вопросе модели используются в производственных системах повсеместно. Довольно большой используется в большом университете / учебном заведении, в котором я работаю. Они специально используют подход длинной узкой таблицы для отображения данных, собранных многими различными системами сбора данных.

Кроме того, Google недавно выпустил свой внутренний протокол обмена данными, буфер протокола, в виде открытого исходного кода через сайт своего кода. Система баз данных, созданная на основе этого подхода, была бы весьма интересной.

Проверьте следующее:

Модель сущность-атрибут-значение

Буфер протокола Google

sql уже предоставляет способ изменить вашу схему: команда ALTER.

просто имейте таблицу, в которой перечислены поля, которые пользователи не могут изменять, и напишите приятный интерфейс для ALTER.

Я сделал это в реальном проекте:

База данных состояла из одной таблицы с одним полем, представляющим собой массив из 50. Для нее был установлен индекс «слово». Все данные не имели типа, поэтому «индекс слов» работал, как ожидалось. Числовые поля были представлены в виде символов, а фактическая сортировка производилась на стороне клиента. (При необходимости все еще можно иметь несколько полей массива для каждого типа данных).

Схема логических данных для логических таблиц хранилась в одной базе данных с другим «типом» строки таблицы (первый элемент массива). Он также поддерживает простое управление версиями в стиле копирования при записи с использованием того же поля типа.

Преимущества:

1. Вы можете переставлять и добавлять / удалять столбцы динамически, без необходимости дампа / перезагрузки базы данных. Любые новые данные столбца могут быть установлены на начальное значение (виртуально) за нулевое время.
2. Фрагментация минимальна, поскольку все записи и таблицы имеют одинаковый размер, иногда это дает лучшую производительность.
3. Вся схема таблицы виртуальная. Возможна любая логическая структура схемы (даже рекурсивная или объектно-ориентированная).
4. Он хорош для данных с возможностью однократной записи, в основном для чтения, без удаления / пометки как удаленные (большинство веб-приложений действительно таковы).

Недостатки:

1. Индексирование только полными словами, без сокращений,
2. Возможны сложные запросы, но с небольшим снижением производительности.
3. Зависит от того, поддерживает ли ваша предпочитаемая система баз данных массивы и индексы слов (она была добавлена ​​в СУБД PROGRESS).
4. Реляционная модель существует только в голове программиста (т.е. только во время выполнения).

И теперь я думаю, что следующим шагом может быть реализация такой базы данных на уровне файловой системы. Это могло быть относительно легко.

То, что вы предлагаете, не ново. Множество людей пробовали это ... большинство из них обнаружили, что они гонятся за "бесконечной" гибкостью и вместо этого получают гораздо, намного меньше этого. Это «мотель тараканов» в дизайне баз данных - данные входят, но получить их практически невозможно. Попробуйте концептуально написать код для ЛЮБЫХ ограничений, и вы поймете, что я имею в виду.

Конечным результатом обычно является система, которую НАМНОГО сложнее отлаживать, поддерживать и которая полна проблем с согласованностью данных. Это не случай всегда, но чаще всего так оно и заканчивается. В основном потому, что программисты не видят приближающегося крушения поезда и не умеют защищаться от него. Кроме того, часто оказывается, что «бесконечная» гибкость на самом деле не так уж и необходима; это очень неприятный "запах", когда команда разработчиков получает спецификацию, в которой говорится: "Черт возьми, я понятия не имею, какие данные они собираются разместить здесь, так что пусть они помещают КАКИЕ-ЛИБО" ... и конечные пользователи в порядке иметь предопределенные типы атрибутов, которые они могут использовать (создайте код общего номера телефона и позвольте им создать любой из них - это тривиально в хорошо нормализованной системе и обеспечивает гибкость и целостность!)

Если у вас очень хорошая команда разработчиков и вы хорошо осведомлен из тех проблем, которые вам придется преодолеть с помощью этого дизайна, вы можете успешно написать хорошо спроектированную, но не очень глючную систему. Большую часть времени.

Зачем же начинать, когда шансы так много против вас?

Не верите мне? Google "One True Lookup Table" или "дизайн единой таблицы". Некоторые хорошие результаты: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10678084117056

http://thedailywtf.com/Comments/Tom_Kyte_on_The_Ultimate_Extensibility.aspx?pg=3

http://www.dbazine.com/ofinterest/oi-articles/celko22

http://thedailywtf.com/Comments/The_Inner-Platform_Effect.aspx?pg=2

Создать 2 базы данных

• DB1 содержит статические таблицы и представляет «реальное» состояние данных.
• DB2 позволяет пользователям делать с ними все, что они хотят - им (или вам) придется писать код для заполнения своих таблиц нечетной формы из DB1.

Как говорили некоторые другие, не делайте этого, если у вас нет другого выбора. Один из случаев, когда это требуется, - это если вы продаете готовый продукт, который должен позволять пользователям записывать пользовательские данные. Продукция моей компании попадает в эту категорию.

Если вам действительно нужно позволить своим клиентам делать это, вот несколько советов:
- Создайте административный инструмент крепкий для внесения изменений в схему и не допускайте внесения этих изменений каким-либо другим способом. - Сделайте это административной функцией; не позволяйте обычным пользователям получить к нему доступ. - Регистрируйте каждую деталь о каждом изменении схемы. Это поможет вам отладить проблемы, а также предоставит вам данные CYA, если клиент сделает что-то глупое.

Если вы можете делать эти вещи успешно (особенно первую), то подойдет любая из упомянутых вами архитектур. Я предпочитаю динамически изменять объекты базы данных, потому что это позволяет вам использовать возможности запросов вашей СУБД при доступе к данным, хранящимся в настраиваемых полях. Остальные три варианта требуют, чтобы вы загружали большие блоки данных, а затем выполняли большую часть обработки данных в коде.

У меня есть аналогичное требование, и я решил использовать MongoDB без схемы.

MongoDB (from "humongous") is an open source, scalable, high-performance, schema-free, document-oriented database written in the C++ programming language. (Wikipedia)

Особенности:

• имеет богатую функциональность запросов (возможно, ближе всего к базам данных SQL)
• готово к производству (Foursquare, sourceforge используйте его)

Lowdarks (то, что вам нужно понять, чтобы вы могли правильно использовать mongo):

• нет транзакций (на самом деле у него есть транзакции, но только по атомарным операциям)
• вот этот материал: http://ethangunderson.com/blog/two-reasons-to-not-use-mongodb/
• долговечность ... в основном, связанные с КИСЛОТОЙ

Подход EAV, который я считаю лучшим подходом, но требует больших затрат

Я знаю, что это старая тема, но думаю, что она никогда не теряет актуальности. Я сейчас разрабатываю что-то подобное. Вот мой подход. Я использую настройку сервера с MySQL, Apache, PHP и Zend Framework 2 в качестве среды приложения, но она также должна работать с любыми другими настройками.

Вот простое руководство по внедрению, вы можете развить его самостоятельно.

Вам нужно будет реализовать свой собственный интерпретатор языка запросов, потому что эффективный SQL будет слишком сложным.

Пример:

select id, password from user where email_address = "[email protected]"

Схема физической базы данных:

Таблица "спецификации": (должна быть кэширована на вашем уровне доступа к данным)

• id: int
• parent_id: int
• имя: varchar (255)

Таблица 'items':

• id: int
• parent_id: int
• spec_id: int
• данные: varchar (20000)

Содержание таблицы «спецификации»:

• 1, 0, 'пользователь'
• 2, 1, 'email_address'
• 3, 1, 'пароль'

Содержание таблицы 'items':

• 1, 0, 1, ''
• 2, 1, 2, '[email protected]'
• 3, 1, 3, "мой пароль"

Перевод примера на наш собственный язык запросов:

select id, password from user where email_address = "[email protected]"

в стандартном SQL будет выглядеть так:

select 
    parent_id, -- user id
    data -- password
from 
    items 
where 
    spec_id = 3 -- make sure this is a 'password' item
    and 
    parent_id in 
    ( -- get the 'user' item to which this 'password' item belongs
        select 
            id 
        from 
            items 
        where 
            spec_id = 1 -- make sure this is a 'user' item
            and 
            id in 
            ( -- fetch all item id's with the desired 'email_address' child item
                select 
                    parent_id -- id of the parent item of the 'email_address' item
                from 
                    items 
                where 
                    spec_id = 2 -- make sure this is a 'email_address' item
                    and
                    data = "[email protected]" -- with the desired data value
            )
    )

Вам нужно будет кэшировать таблицу спецификаций в ассоциативном массиве или хэш-таблице или что-то подобное, чтобы получить spec_id из имен спецификаций. В противном случае вам нужно будет добавить дополнительные накладные расходы SQL, чтобы получить spec_id из имен, как в этом фрагменте:

Плохой пример, не используйте это, избегайте этого, вместо этого кешируйте таблицу спецификаций!

select 
    parent_id, 
    data 
from 
    items 
where 
    spec_id = (select id from specs where name = "password") 
    and 
    parent_id in (
        select 
            id 
        from 
            items 
        where 
            spec_id = (select id from specs where name = "user") 
            and 
            id in (
                select 
                    parent_id 
                from 
                    items 
                where 
                    spec_id = (select id from specs where name = "email_address") 
                    and 
                    data = "[email protected]"
            )
    )

Надеюсь, вы уловили идею и сможете сами определить, применим ли этот подход для вас.

Наслаждаться! :-)

ElasticSearch. Вы должны учитывать это, особенно если вы имеете дело с наборами данных, которые можно разделить по дате, вы можете использовать JSON для своих данных и не привязаны к использованию SQL для извлечения данных.

ES определяет вашу схему для любых новых полей JSON, которые вы отправляете, либо автоматически, с подсказками, либо вручную, которые вы можете определить / изменить с помощью одной HTTP-команды («сопоставления»). Хотя он не поддерживает SQL, у него есть отличные возможности поиска и даже агрегирования.

На вики c2.com была исследована идея «Динамический реляционный». Вам НЕ нужен администратор базы данных: столбцы и таблицы создаются при записи, если только вы не начнете добавлять ограничения, чтобы заставить его действовать как традиционная СУБД: по мере развития проекта вы можете постепенно «блокировать его».

Концептуально вы можете думать о каждой строке как о выражении XML. Например, запись о сотруднике может быть представлена ​​как:

<employee lastname = "Li" firstname = "Joe" salary = "120000" id = "318"/>

Это означает, что нет должен быть реализован как XML, это просто удобная концептуализация. Если вы запрашиваете несуществующий столбец, например «SELECT madeUpColumn ...», он обрабатывается как пустой или пустой (если добавленные ограничения не запрещают это). И можно использовать SQL, хотя нужно быть осторожным с сравнениями из-за модели подразумеваемого типа. Но кроме обработки типов, пользователи динамической реляционной системы будут чувствовать себя как дома, потому что они могут использовать большую часть своих существующих знаний РСУБД. Теперь, если бы кто-нибудь просто построил его ...

Я знаю, что это очень старый пост, и за последние 11 лет многое изменилось, но подумал, что добавлю это, так как это может быть полезно для будущих читателей. Одна из причин, по которой я и мои соучредители создали HarperDB, заключается в том, чтобы изначально реализовать динамическую схему в едином, не дублированном наборе данных, обеспечивая при этом полную возможность индексации. Вы можете прочитать об этом здесь:
https://harperdb.io/blog/dynamic-schema-the-harperdb-way/