Один стол или много?

одна таблица и поле "тип" были бы моим предложением

Я бы хотел иметь одну таблицу для всех ссылок, но дополнительные таблицы, такие как BookReferences и т. д., Для метаданных, не применимых для всех типов ссылок.

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

Размещение всего в одной таблице с большим количеством нулей может показаться более простым решением, но на самом деле это приведет к множеству проблем. Например: с отдельными таблицами вы можете определить, какие поля являются требуется для каждой BookReference, но если все находится в одной таблице, каждое поле должно допускать значение NULL и, следовательно, необязательно. Также было бы проще вставить недопустимые данные, например ссылку на книгу, которая также ошибочно содержит ненулевое имя журнала.

Обновлено: некоторые люди, кажется, боятся присоединений. Не бойтесь присоединения! Если вы используете одно и то же соединение в нескольких запросах, это действительно будет утомительно, но в этом случае соединение должно быть определено в Посмотреть, и вы должны запрашивать это представление. Представления на самом деле являются базовой абстракцией в реляционных базах данных, и вы должны использовать их по тем же причинам, по которым вы используете функции в коде: чтобы избежать повторения, а также для инкапсуляции и создания абстракций.

Обновлено: есть некоторые комментарии относительно производительности. О производительности схем БД заранее догадаться очень сложно, потому что это часто не интуитивно понятно. Например, соединение нескольких таблиц может быть быстрее, чем полное сканирование одной таблицы - все зависит от типа запроса, природы данных, доступных индексов и так далее. Кроме того, во многих системах баз данных вы можете использовать такие функции, как материализованные представления, для оптимизации производительности для различных запросов без ущерба для логической модели. «Денормализация для производительности» в наши дни - это в основном культ карго, ИМХО, если вы не Google или Flickr.

Вы спрашиваете о нормализации базы данных. Джефф Этвуд написал об этом в своем посте Может быть, нормализация - это не нормально. Это хорошее чтение.

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

Наличие отдельных таблиц упростило бы добавление нового ссылочного типа (и автоматическое создание формы для него!), А поиск не стал бы труднее.

Rails поддерживает однотабличное наследование и полиморфные типы ActiveRecord. Я бы посоветовал изучить их - у ActiveRecord есть некоторые мнения о том, как должна быть структурирована база данных.

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

Допустим, моя-одна-большая-таблица выглядит так:

1 id
2 типа
3 обычных журнала для книг и журналов 4 отдельных поля для книги 5 специализированных журналов

Если меня просто интересуют книги, я могу создать представление или просто sql, например:

create view book as  
select id, field_common-to-book-and-journal, field-specific-to-book
from my-one-big-table
where type = 'book'

Таким образом, когда я хочу, легко смоделировать, что данные находятся в отдельных таблицах.

Но если я начну с размещения данных в отдельных таблицах, тогда я напишу SQL следующим образом:

select id, field-common-to-book-and-journal from books
union
select id, field-common-to-book-and-journal from journal-articles
union
.... etc, for each type

Я не знаю о других базах данных, но объединение в SQL Server может быть дорогостоящим, и существуют ограничения при работе с такими типами данных, как ntext.

Если вы последуете совету olavk, тогда ваш SQL для объединения типов в один запрос будет выглядеть так:

select 
    common.id, 
    common.field-common-to-book-and-journal, 
    book.field-specific-to-book 
    journal.field-specific-to-journal
from common-table common
left outer join book-specific-table book on 
left outer join journal-specific-table journal on
... etc, for each type

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

Есть еще один вариант: не тот, который я полностью одобряю, но это еще один вариант:

Используйте три таблицы:

refs (id, title, refType)
-- title of the reference, and what type of reference it is

fieldDef (id, fieldName, refType, dataType)
-- name of the field, which reference types it applies to, and
-- what type of data is stored in these fields (ISDN number, date, etc)

fields (refId, fieldId, value)
-- where you actually add data to the references.

refType может быть типом ссылки, и если вы сделаете его целым числом со значениями, увеличивающимися на степень двойки (1, 2, 4, 8 ...), то их можно сложить вместе, чтобы создать битовую маску в таблице fieldDef.

Плюсы: очень простой и расширяемый. Если вы придумаете другой тип ссылки или новый тип поля для существующего ссылочного типа, его можно будет добавить очень быстро. Формы могут быть автоматически созданы для каждого ссылочного типа. Все данные хранятся в одном месте, что означает, что вам не нужно отслеживать несколько схем (схемы?) для CRUD операции.

Минусы: это то, на чем написан The Daily WTF. Операторы выбора могут быть очень запутанными и сложными. База данных не может выполнять проверку типов (например, для дат и т. д.), А общее поле «значение» не будет оптимизировано для данных, которые в нем хранятся.

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

Основная проблема заключается в том, что разработчики таких таблиц склонны опускать ограничения, необходимые для обеспечения целостности данных. Например, OP говорит:

a journal reference requires both a journal title and an article title, and also a page number, whereas a book requires a publisher and a publication date which journal articles do not require

... что подразумевает следующие ограничения:

CONSTRAINT a_journal_must_have_a_journal_title
   CHECK ( type <> 'journal' OR journal_title IS NOT NULL );

CONSTRAINT a_journal_must_have_an_article_title 
   CHECK ( type <> 'journal' OR article_title IS NOT NULL );

CONSTRAINT a_journal_must_have_a_page_number 
   CHECK ( type <> 'journal' OR page_number IS NOT NULL );

CONSTRAINT a_journal_cannot_have_a_publisher 
   CHECK ( type <> 'journal' OR publisher IS NULL );

CONSTRAINT a_journal_cannot_have_a_publication_date 
   CHECK ( type <> 'journal' OR publication_date IS NULL );

CONSTRAINT a_book_cannot_have_a_journal_title 
   CHECK ( type <> 'book' OR journal_title IS NULL );

CONSTRAINT a_book_cannot_have_a_article_title 
   CHECK ( type <> 'book' OR article_title IS NULL );

CONSTRAINT a_book_cannot_have_a_page_number 
   CHECK ( type <> 'book' OR page_number IS NULL );

CONSTRAINT a_book_must_have_a_publisher 
   CHECK ( type <> 'book' OR publisher IS NOT NULL );

CONSTRAINT a_jbook_must_have_a_publication_date 
   CHECK ( type <> 'book' OR publication_date IS NOT NULL );

... и я подозреваю, что это только верхушка айсберга!

Я надеюсь, что после написания нескольких сотен таких ограничений у дизайнера возникнут сомнения по поводу всех этих столбцов, допускающих значение NULL :)

Я не считаю необходимость присоединяться к столам особенно утомительной; Я бы выбрал более нормализованный подход.

В прошлом я использовал подкатегории: одну таблицу со всеми полями общий внутри нее, а затем несколько таблиц, которые могут иметь отношение ноль или один к «основной» таблице.

Пример ниже похож на то, что мы используем «в дикой природе»; он в основном строит иерархическую структуру данных, где каждый узел может быть папкой или документом:

CREATE TABLE Node (
  Id int identity primary key,
  ParentId int null references Node.ParentId,
  Name varchar(50) not null,
  Description varchar(max) null
)

CREATE TABLE Doc (
  Id int primary key references Node.Id,
  FileExtension char(3) not null,
  MimeType varchar(50) not null,
  ContentLength bigint not null,
  FilePathOnDisk varchar(255)
)

CREATE TABLE Folder (
  Id int primary key references Node.Id,
  ReadOnly bit not null
)

Итак, ваш GetFolder sproc будет делать:

SELECT n.Id, n.ParentId, n.Name, n.Description, f.ReadOnly
FROM Node n 
JOIN Folder f ON n.Id = f.Id
WHERE f.Id = @Id

Это прекрасно трансформируется в наследование на основе классов:

public class Folder : Node
{
  public bool IsReadOnly { get; set; }
  ...etc
}

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

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

Создайте одну таблицу, в которой будут храниться общие атрибуты для всех ссылок.

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

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

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

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

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

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

Как насчет того и другого? Ешьте свой торт и ешьте его тоже!

Где-то между «одной большой таблицей» и «полностью нормализованной» БД есть еще один вариант, который действительно сочетает в себе лучшее из обоих миров: вы можете использовать что-то под названием материализованные представления, которое похоже на представления в том, что они столь же гибкие, и вы запрашиваете столько же таблицы по мере необходимости, настройка всех объединений и т. д., но они также похожи на таблицы в том смысле, что результаты фактически хранятся в таблице.

Приятно то, что как только вы это настроите и решите, когда его следует обновлять (каждый раз, когда изменяется одна из базовых таблиц sis, или, может быть, только один раз за ночь), вам больше не о чем беспокоиться. Вы можете запросить материализованное представление, как если бы это была одна большая таблица (потому что это так), и производительность будет выше (быстрее, чем при использовании оператора select, стоящего за ним). Самое главное, у вас нет проблем с поддержанием целостности данных. Это то, что должна обрабатывать БД.

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

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

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

Хранилище данных :)

Некоторое время назад я обсуждал эти вопросы со своим начальником. Конечно, я не смог доказать, что «подход иерархическая многотабличная» (см. ответ олавка) лучше, но я это почувствовал! Я бы всегда выбрал этот метод. Одна корневая таблица со всеми полями, которые есть у сущностей, и 1-1 дочерние таблицы с полями, которые у них нет общих. При необходимости этот подход можно распространить на большее количество дочерних таблиц, если бизнес-логика и другие сущности будут что-то извлекать из этого. То есть не думаю, что с этим нужно переборщить.

Я также против создания отдельные "дочерние" таблицы без корневой таблицы, где в каждой таблице есть копии одних и тех же полей. Я думаю, что Кори ответ предлагает такой подход как пример плохой многостоловой модели, и он также его критикует. Я хотел бы добавить, что необходимость писать соединения - не главная проблема. Это вообще не проблема, поскольку большинство запросов к базе данных имеют много объединений, и это нормальное явление. Создать отношения с другими таблицами сложно - вам всегда нужны Id и TypeId, чтобы знать, какая таблица на них ссылается. В случае корневой таблицы вам нужен только Id.