У меня могут быть "идентификационные данные" (уникальный, не повторяясь) промежуток столбца несколько таблиц? Например, скажем, у меня есть две таблицы: Книги и Авторы.
Authors
AuthorID
AuthorName
Books
BookID
BookTitle
Столбец BookID и столбец AuthorID являются столбцами идентификационных данных. Я хочу, чтобы часть идентификационных данных охватила оба столбца. Так, если существует AuthorID со значением 123, то не может быть BookID со значением 123. И наоборот.
Я надеюсь, что это имеет смысл.
Действительно ли это возможно?
Спасибо.
Почему я хочу сделать это? Я пишу приложение MVC APS.NET. Я создаю раздел комментария. У авторов могут быть комментарии. Книги могут иметь комментарии. Я хочу смочь передать идентификатор объекта (книжный идентификатор, или идентификатор автора) к действию и иметь действие тянут все соответствующие комментарии. Действие не будет заботиться, является ли это книга или автор или что бы то ни было. Разумный звук?
Короткий ответ: нет, вы не можете этого сделать (по крайней мере, в MS SQL Server до 2008 года).
Вы можете создать новую таблицу, «CommentableEntity», вставить туда свой столбец идентификаторов, затем определить внешние ключи в Authors и Books, чтобы ссылаться на нее как на родительскую таблицу, а затем выполнить один из нескольких трюков, чтобы гарантировать, что данное значение идентификатора не назначается обеим таблицам ... но это плохая идея, потому что построенная вами модель данных подразумевает, что Авторы и Книги являются связанными видами данных, а на самом деле это не так.
У вас может быть отдельная таблица, Комментарии, столбец идентификаторов, а также столбец CommentId как для авторов, так и для книг. Однако это ограничило бы каждую книгу и автора только одним комментарием.
Я бы, наверное, добавил столбец типа «CommentorType» в таблицу комментариев и поставил там флажок, указывающий на источник комментария («A» для автора, «B» для книги). Создайте первичный ключ на «CommentorId + CommentorType», и он должен работать достаточно хорошо - и было бы тривиально добавлять другие типы комментаторов по мере расширения системы.
Даже если вы можете поместить идентификационную последовательность в несколько таблиц, ваша таблица комментариев не сможет ссылаться на оба столбца в одном внешнем ключе.
Лучшим способом сделать это с точки зрения теории проектирования реляционных баз данных было бы создание двух таблиц комментариев. Но, очевидно, вы хотите избежать этого, вероятно, по причинам повторного использования кода.
Самый простой прагматический подход - поместить два столбца внешнего ключа в таблицу комментариев и просто сделать один нулевым, а другой ненулевым для каждого комментария.
Другой подход, который может быть лучшим компромиссом, заключается в следующем. В своем вопросе вы ссылаетесь на «идентификатор объекта». Итак, сделайте таблицу сущностей! Затем авторы, книги и комментарии могут ссылаться на эту таблицу.
Отредактировано для добавления:
Филип Келли, Рэй и (я думаю) Артик предложили изменить таблицу комментариев, добавив entity_id
, который может относиться к book_id
или author_id
и какой-то флаг ( char (1)
, tinyint
и логическое
, соответственно), указывает, о каком из них идет речь.
Это не лучшее решение по многим причинам, как прагматическим (включая целостность данных, отчетность, эффективность), так и теоретическим.
Первая и наиболее очевидная проблема - это проблема целостности данных. Система реляционной базы данных всегда должна нести ответственность за поддержание целостности своих собственных данных, и существуют естественные и предпочтительные способы, которыми БД предназначена для этого. Одним из наиболее важных из этих механизмов является система внешнего ключа. Если столбец comment.entity_id
должен ссылаться на оба book.book_id
и author.author_id
, то внешний ключ не может быть создан для этого столбца.
Конечно, вы можете включить проверку хранимых процедур DML (вставка, обновление, удаление) для проверки ссылок, но это быстро превратится в большой беспорядок, поскольку будут задействованы все операции DML для всех трех таблиц.
И это подводит нас к проблеме эффективности. Каждый раз, когда выполняется запрос к таблице comment
, он потребует объединения либо с таблицей автора
, либо с таблицей book
, либо с обоими.Система генерации плана запроса не будет иметь внешних ключей, доступных для оптимизации, поэтому ее производительность вполне может снизиться.
Тогда есть проблемы с этой схемой в отчетности. Любая система создания отчетов будет иметь проблемы с такой системой. Конечно, это не будет проблемой для опытных программистов, но любые пользовательские специальные отчеты должны будут моделировать логику, когда event_id
означает то или иное, и это может быть довольно плохим иметь дело.Возможно, вы никогда не будете использовать инструменты для создания отчетов в этой базе данных. Но опять же, никто не знает, где в конечном итоге будет использоваться база данных. Почему бы не работать с системой, чтобы учесть что-либо?
И это подводит нас к теоретическим проблемам.
В теории реляционных баз данных каждая строка (также известная как «кортеж») в каждой таблице («переменная отношения») представляет предложение о реальном мире. При разработке таблицы необходимо определить форму этого предложения. Давайте посмотрим на несколько примеров того, как это может работать.
comment (comment_id int, comment_type char(1), entity_id int,
user_id int, comment_text nvarchar(max), comment_date datetime)
/* comment_id identifies a comment (comment_text) that a user (user_id)
has made about a book (entity_id if comment_type = 'B') or author
(entity_id if comment_type = 'A') at a particular date and
time (comment_date).*/
Здесь ясно, что столбец (или «атрибут») с именем entity_id
выполняет двойную функцию. На самом деле он ничего не представляет, кроме ссылки на другой столбец. Это работоспособно, но неудовлетворительно.
comment (comment_id int, book_id int, author_id int, user_id int,
comment_text nvarchar(max), comment_date datetime)
/* comment_id identifies a comment (comment_text) that a user (user_id)
has made about a book (book_id if not null) or author (author_id if
not null) at a particular date and time (comment_date). */
Это покупает нам внешние ключи, которые являются самым большим упущением по сравнению с первой версией. Но это все еще не совсем удовлетворительно, если только один комментарий не может относиться как к книге, так и к автору (что может быть разумным). Столбцы, допускающие значение NULL, являются предупреждением о том, что что-то не так с дизайном, и это может иметь место и здесь. Ограничение проверки может быть необходимо, чтобы избежать комментария, который вообще ни к чему не относится, или как к книге, так и к автору, если это недопустимо.
С теоретической точки зрения (и, следовательно, с моей точки зрения :)) существует очевидный лучший вариант:
book_comment (book_comment_id int, book_id int, user_id int,
comment_text nvarchar(max), comment_date datetime)
/* book_comment_id identifies a comment (comment_text) that a
user (user_id) has made about a book (book_id) at a particular
date and time (comment_date). */
author_comment (author_comment_id int, author_id int, user_id int,
comment_text nvarchar(max), comment_date datetime)
/* author_comment_id identifies a comment (comment_text) that a
user (user_id) has made about an author (author_id) at a particular
date and time (comment_date). */
Этот последний вариант обеспечит максимальную эффективность, целостность данных и простоту отчетности. И единственная трата будет заключаться в том, что хранимые процедуры DML должны будут помещать комментарии в нужные таблицы, что не представляет большого труда, поскольку они в любом случае должны знать, о чем идет речь в комментариях.
Если ваш план состоял в том, чтобы получить все комментарии к книге или автору сразу, то вы можете легко создать представление поверх этих таблиц, которое воспроизводит другие проекты, если это то, что вы хотите сделать.
create view comments as
select
book_comment_id as comment_id,
book_id as entity_id,
comment_text,
'B' as comment_type
from book_comment
union
select
author_comment_id as comment_id,
author_id as entity_id,
comment_text,
'A' as comment_type
from author_comment
В качестве предложения - попробуйте использовать такие таблицы, как ComentId, EntityId, isBook, Comment для комментариев. isBook - это логический тип, и его не так уж много. Ваша концепция нехороша с точки зрения отношений.
Сервер SQL не поддерживает это. Вы можете свернуть свой собственный с таблицей идентификаторов, но это будет больше работы, чем она того стоит.
Я предлагаю, чтобы ваша таблица комментариев выглядела так:
comment_id int identity
comment_type tinyint
entity_id int
comment_type указывает, принадлежит ли комментарий книге, автору или чему-то еще, что вы добавите в будущем. entity_id - это идентификатор книги, автора или чего угодно. В этой схеме не имеет значения, совпадают ли идентификаторы книги или автора.
Или, если вы можете переключиться на оракул, используйте последовательность:)