Вопрос по sql, postgresql, mysql, sql-server, oracle – База данных в базе данных (дизайн таблицы) [дубликат]

0

Possible Duplicate:
Database design to create tables on the fly

У меня есть необходимость создать базу данных в базе данных. Этот вопрос связан сэтот, Я попытаюсь объяснить, что я пытаюсь сделать подробно, и привести примеры в коде. В принципе, я хочу умение создавать динамические таблицы в базе данных. Например, у меня будет веб-страница, которая позволяет пользователям создавать свои собственные таблицы со столбцами и данными. Вот дизайн базы данных, который я придумал:

aColumn
aDataType
aRow
aTable
zBit
zDateTime
zMoney
zNumber
zText

Таблицы, которые начинаются сz это данные, в которые входят конкретные данные, такие как целые числа, значения даты и времени и т. д. aColumn - это столбцы, которые принадлежат определенной таблице. aRow определяет конкретную строку в таблице. Вот дизайн базы данных:

aTable: Id, name
aColumn: Id, Name, aTable, aDataType
aDataType: Id, Name
aRow: Id, aTable
zBit: Id, aRow, aColumn, Data(Bit)
zDateTime: Id, aRow, aColumn, Data (DateTime)
zMoney: Id, aRow, aColumn, Data (Money)
zNumber: Id, aRow, aColumn, Data (INT)
zText: Id, aRow, aColumn, Data (nvarchar(MAX))

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

aTable

Id          Name
1           Users

aColumns

Id          Name           aTable       aDataType
1           Name           1            2
2           UserId         1            1
3           Occupation     1            2

aDataType

Id          Name
1           Number
2           Text

aRow

Id          aTable
1           1
2           1

aNumber

Id          aRow           aColumn      Data
1           1              1            1245
2           2              2            56

aText

Id          aRow           aColumn      Data
1           1              1            Sara
2           2              1            Jake

Все остальные таблицы z * пусты

Вот запрос для создания динамической таблицы:

select t.[Id] as [Table], c.Name as [Column], dt.Name as [DataType], r.[Id] as [Row], cast(v.Data as nvarchar(MAX)) as Data from [pod].[dbo].[aTable] t
INNER JOIN [pod].[dbo].[aColumn] c on t.Id = c.[aTable]
INNER JOIN [pod].[dbo].[aDataType] dt on c.[aDataType] = dt.Id
INNER JOIN [pod].[dbo].[aRow] r on t.[Id] = r.[aTable]
INNER JOIN [pod].[dbo].[zBit] v on c.[Id] = v.aColumn and r.[Id] = v.[aRow]
UNION ALL
select t.[Id] as [Table], c.Name as [Column], dt.Name as [DataType], r.[Id] as [Row], cast(v.Data as nvarchar(MAX)) as Data from [pod].[dbo].[aTable] t
INNER JOIN [pod].[dbo].[aColumn] c on t.Id = c.[aTable]
INNER JOIN [pod].[dbo].[aDataType] dt on c.[aDataType] = dt.Id
INNER JOIN [pod].[dbo].[aRow] r on t.[Id] = r.[aTable]
INNER JOIN [pod].[dbo].[zDateTime] v on c.[Id] = v.aColumn and r.[Id] = v.[aRow]
UNION ALL
select t.[Id] as [Table], c.Name as [Column], dt.Name as [DataType], r.[Id] as [Row], cast(v.Data as nvarchar(MAX)) as Data from [pod].[dbo].[aTable] t
INNER JOIN [pod].[dbo].[aColumn] c on t.Id = c.[aTable]
INNER JOIN [pod].[dbo].[aDataType] dt on c.[aDataType] = dt.Id
INNER JOIN [pod].[dbo].[aRow] r on t.[Id] = r.[aTable]
INNER JOIN [pod].[dbo].[zMoney] v on c.[Id] = v.aColumn and r.[Id] = v.[aRow]
UNION ALL
select t.[Id] as [Table], c.Name as [Column], dt.Name as [DataType], r.[Id] as [Row], cast(v.Data as nvarchar(MAX)) as Data from [pod].[dbo].[aTable] t
INNER JOIN [pod].[dbo].[aColumn] c on t.Id = c.[aTable]
INNER JOIN [pod].[dbo].[aDataType] dt on c.[aDataType] = dt.Id
INNER JOIN [pod].[dbo].[aRow] r on t.[Id] = r.[aTable]
INNER JOIN [pod].[dbo].[zMoney] v on c.[Id] = v.aColumn and r.[Id] = v.[aRow]
UNION ALL
select t.[Id] as [Table], c.Name as [Column], dt.Name as [DataType], r.[Id] as [Row], cast(v.Data as nvarchar(MAX)) as Data from [pod].[dbo].[aTable] t
INNER JOIN [pod].[dbo].[aColumn] c on t.Id = c.[aTable]
INNER JOIN [pod].[dbo].[aDataType] dt on c.[aDataType] = dt.Id
INNER JOIN [pod].[dbo].[aRow] r on t.[Id] = r.[aTable]
INNER JOIN [pod].[dbo].[zNumber] v on c.[Id] = v.aColumn and r.[Id] = v.[aRow]
UNION ALL
select t.[Id] as [Table], c.Name as [Column], dt.Name as [DataType], r.[Id] as [Row], cast(v.Data as nvarchar(MAX)) as Data from [pod].[dbo].[aTable] t
INNER JOIN [pod].[dbo].[aColumn] c on t.Id = c.[aTable]
INNER JOIN [pod].[dbo].[aDataType] dt on c.[aDataType] = dt.Id
INNER JOIN [pod].[dbo].[aRow] r on t.[Id] = r.[aTable]
INNER JOIN [pod].[dbo].[zText] v on c.[Id] = v.aColumn and r.[Id] = v.[aRow]

Вот один фрагмент этого запроса:

select t.[Id] as [Table], c.Name as [Column], dt.Name as [DataType], r.[Id] as [Row], cast(v.Data as nvarchar(MAX)) as Data from [pod].[dbo].[aTable] t
INNER JOIN [pod].[dbo].[aColumn] c on t.Id = c.[aTable]
INNER JOIN [pod].[dbo].[aDataType] dt on c.[aDataType] = dt.Id
INNER JOIN [pod].[dbo].[aRow] r on t.[Id] = r.[aTable]
INNER JOIN [pod].[dbo].[zText] v on c.[Id] = v.aColumn and r.[Id] = v.[aRow]

Как вы можете видеть, часть данных (таблицы z *) идентифицируется строкой и столбцом. Когда я запускаю этот запрос, я получаю это:

Results

Table       Column         DataType     Row           Data
1           UserId         Number       1             1245          
1           UserId         Number       2             56
1           Name           Text         1             Sara
1           Name           Text         2             Jake

Вот мои желаемые результаты: (Я не уверен, как превратить эти строки в столбцы, если столбцыunknown)

Row         UserId       Name
1           1245         Sara
2           56           Jake

Big Problem Эта таблица должна иметь 3 столбца помните?

aColumns

Id          Name           aTable       aDataType
1           Name           1            2
2           UserId         1            1
3           Occupation     1            2

Итак, мои последние ожидаемые результаты:

Row         UserId       Name         Occupation
1           1245         Sara         NULL
2           56           Jake         NULL

В результатах мне нужно также сортировать столбцы. Это вообще возможно? Какие базы данных поддерживают такую функциональность. Я широко открыт для любой базы данных, которая может сделать это.

I have a need to create a database within a database. Why? Что не так с простым добавлением и удалением столбцов и / или таблиц. a_horse_with_no_name
@ChrisShain - проблема с динамическим созданием новых строк / столбцов заключается в том, как вы пишете свои запросы, поскольку каждый запрос должен искать, какие столбцы существуют, а затем динамически позволять людям выбирать, что извлечь. В основном вы создаете основу для модели EAV в процессе. James Black
Увидетьen.wikipedia.org/wiki/Inner-platform_effect а такжеthedailywtf.com/Articles/The_Inner-Platform_Effect.aspx, Почему вы изобретаете колесо, а не просто заставляете ваше приложение динамически создавать реальные строки и столбцы? Chris Shain
Правильно, но вы должны делать это и с вашим подходом. MySQL, Oracle и SQL Server имеют встроенные методы для получения структуры существующей базы данных. Большим преимуществом EAV, IMHO, является способность эффективно хранить разреженные данные, что уже делают многие движки баз данных (см. Хранилища, ориентированные на столбцы SQL Server) Chris Shain
Добро пожаловать в мир БД. Я подозреваю, что каждый разработчик базы данных проходит этот этап, пока не узнает термин «EAV». и начать читать об этом. Однако, если вы просто играете, развлекаетесь и сами изучаете это нелегко - часто это лучший способ учиться. Удачи :) Jeffrey Kemp

Ваш Ответ

2   ответа
1

Кросстабуляция запрос к схеме EAV. Некоторые базы данных поддерживают это посредством расширений стандарта SQL, другие не поддерживают его вообще. Для мобильности вы должны сделать это в своем приложении. PostgreSQL предлагаетфункция кросс-таблицы в расширении таблицы за это.

Если вы идете внизEAV Путь вы рано или поздно пожалеете. Это полезно в определенных ограниченных обстоятельствах, но плохо подходит для реляционной модели и вызывает много боли и проблем, не последним из которых является ужасная производительность.

Рассмотрим вместо этого:

If at all possible, re-design so you don't need dynamic schema. Probably not possible in your case since your express requirement is a user-editable schema for a web-based database app, but in most cases this is the right choice.

Dynamically create/drop schema with ALTER TABLE, CREATE TABLE, etc. Some databases are much better at this than others. PostgreSQL's transactional DDL can help a lot. Caution is required to avoid this becoming a performance and maintenance nightmare, but it's probably the sanest option if you're trying to model a relational database with dynamic structure.

Key/value stores that are optimised for EAV-like querying; see Key/Value stores. Be careful, many of these systems don't offer full ACID semantics and may have limited query languages, so you can land up doing lots more work in the application.

Storing XML or JSON in the database. You can do that with a relational DB, but you're likely to be better off with a document database. Same caveats as for K/V stores apply. This approach works ok if you're doing all the querying logic in your app and your data sizes aren't too big.

Using database-specific features like PostgreSQL's hstore to support arbitrary key/value storage where required, and use standard relational design where k/v isn't required. If you want relations as output it's still a major PITA that involves inefficient crosstab queries and joining.

Крис Делает хорошую мысль: весь ваш дизайн очень сомнительный. Увидеть:Эффект внутренней платформы а такжеTDWTF 's взять на себя, Серьезно, не ходи туда.

3

Модель значения атрибута сущности (EAV) дизайн для ваших данных тогда.

В основном вы можете иметь таблицу с именами таблиц и некоторые другие метаданные таблиц.

Затем вы можете создать таблицу для каждой из этих строк, чтобы иметь данные столбца, такие как тип данных и имя.

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

Это позволяет динамически создавать таблицы или динамически добавлять / удалять строки.

Для сравнения по реляционным и EAV вы можете посмотреть на этот вопрос:

База данных значений атрибутов сущностей против строгой реляционной модели электронной коммерции

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

Другой способ сделать это с базой данных NoSQL (http://en.wikipedia.org/wiki/NoSQL), так как схема не должна быть установлена, и поэтому вы можете просто сохранить столбцы, необходимые для этой строки.

На этом этапе я бы пошел по пути NoSQL, так как есть много баз данных, которые могут работать, и переизобретения, которые вам нужно сделать, минимальны.

Похожие вопросы