SQL Server 2005: упаковка таблиц по представлениям - за и против
Фон
Я работаю над устаревшей системой автоматизации малого бизнеса (инвентаризация, продажи, закупки и т. д.), Которая имеет единую базу данных, размещенную на SQL Server 2005, и множество клиентских приложений. Основным клиентом (используемым всеми пользователями) является приложение MS Access 2003 (ADP), а другие клиенты включают различные приложения VB / VBA, такие как надстройки Excel и утилиты командной строки.
В дополнение к примерно 60 таблицам (в основном в 3NF) база данных содержит около 200 представлений, около 170 пользовательских функций (в основном скалярных и табличных встроенных) и около 50 хранимых процедур. Как вы могли догадаться, некоторая часть так называемой «бизнес-логики» инкапсулирована в этой массе кода T-SQL (и, таким образом, используется всеми клиентами).
В целом код системы (включая код T-SQL) не очень хорошо организован и, так сказать, очень устойчив к рефакторингу. В частности, схемы большинства таблиц требуют всех видов рефакторинга, малых (например, переименование столбцов) и больших (например, нормализации).
FWIW, у меня довольно большой и достойный опыт разработки приложений (C / C++, Java, VB и еще много чего), но я не администратор базы данных. Итак, если вопрос покажется вам глупым, теперь вы знаете, почему это так. :-)
Вопрос
Размышляя о рефакторинге всего этого беспорядка (конечно, мирным путем), я пришел к следующей идее:
Для каждой таблицы создайте представление-оболочку, которое (а) содержит все столбцы, которые есть в таблице; и (б) в некоторых случаях имеет несколько дополнительных вычисляемых столбцов на основе «реальных» столбцов таблицы.
Типичным (хотя и упрощенным) примером такого дополнительного вычисляемого столбца может быть цена продажи продукта, полученная на основе обычной цены продукта и скидки.
Реорганизуйте весь код (как T-SQL, так и клиентский код VB / VBA) так, чтобы только представления «оболочки» напрямую ссылались на таблицы.
Так, например, даже если приложению или хранимой процедуре необходимо вставить / обновить / удалить записи из таблицы, они будут делать это против соответствующего представления «оболочки таблицы», а не против таблицы напрямую.
Итак, по сути, речь идет о изоляция всех таблиц по представлениям от остальной системы.
Такой подход, кажется, дает много преимуществ, особенно с точки зрения ремонтопригодности. Например:
Когда необходимо переименовать столбец таблицы, это можно сделать, не перезаписывая сразу весь затронутый клиентский код.
Проще реализовать производные атрибуты (проще, чем использование вычисляемых столбцов).
Вы можете эффективно использовать псевдонимы для имен столбцов.
Очевидно, что за все эти преимущества должна быть какая-то цена, но я не уверен, что вижу все скрывающиеся там уловки.
Кто-нибудь пробовал этот подход на практике? Каковы основные подводные камни?
Одним из очевидных недостатков является стоимость поддержки представлений-оболочек в синхронизации с соответствующими таблицами (новый столбец в таблице также должен быть добавлен в представление; столбец, удаленный из таблицы, должен быть удален также из представления; и т. д.). Но эта цена кажется небольшой и справедливой для повышения устойчивости всей кодовой базы.
Кто-нибудь знает другие, более сильные недостатки?
Например, использование всех этих представлений-оболочек вместо таблиц с большой вероятностью окажет негативное влияние на производительность, но будет ли это влияние достаточно существенным, чтобы беспокоиться об этом? Кроме того, при использовании ADODB очень легко получить набор записей, который нельзя обновить, даже если он основан всего на нескольких соединенных таблицах; Итак, будут ли представления "обертки" существенно ухудшить ситуацию? И так далее...
Будем очень благодарны за любые комментарии (особенно из общего опыта).
Спасибо!
P.S. Я натолкнулся на следующую старую статью, в которой обсуждается идея представлений-оберток:
В статье рекомендуется избегать описанного выше подхода. Но ... Я не вижу в статье веских причин против этой идеи. Напротив, в списке веских причин для создания представления почти каждый элемент является именно тем, почему так заманчиво создать представление-оболочку для каждой таблицы (особенно в устаревшей системе, как часть процесса рефакторинга). ).
Статья действительно старая (1999 г.), так что какие бы причины ни были хорошими, они могут быть уже не подходящими (и наоборот). Было бы действительно интересно услышать от кого-то, кто недавно рассмотрел или даже попробовал эту идею с последними версиями SQL Server и MS Access ...
Ответы 3
При разработке базы данных я предпочитаю следующее:
- нет прямого доступа к таблице с помощью кода (но это нормально из хранимых процедур, представлений и функций)
- базовое представление для каждой таблицы, которое включает все столбцы
- расширенное представление для каждой таблицы, которое включает столбцы подстановки (типы, статусы и т. д.)
- хранимые процедуры для всех обновлений
- функции для любых сложных запросов
это позволяет администратору базы данных работать напрямую с таблицей (добавлять столбцы, очищать, вводить данные и т. д.), не нарушая базу кода, и изолирует базу кода от любых изменений, внесенных в таблицу (временных или иных)
при таком подходе могут возникнуть проблемы с производительностью, но пока они не были значительными, а преимущества слоя изоляции несколько раз спасали жизнь.
Я согласен с Комментарий Стивена - в первую очередь потому, что вы используете Access. При перепроектировании этой базы данных чрезвычайно важно уделять внимание плюсам и минусам Access. Я был там, сделал это с помощью внешнего интерфейса Access / серверной части SQL Server (хотя это не был проект ADP).
Я бы добавил, что представления хороши для обеспечения того, чтобы данные не изменялись за пределами форм Access в проекте. Обратной стороной является то, что для всех обновлений требуются хранимые процедуры - если у вас их еще нет, их тоже придется создать.
Вы не заметите никакого снижения производительности для представлений с одной таблицей; SQL Server будет использовать базовую таблицу при построении планов выполнения для любого кода, использующего эти представления. Я рекомендую вам привязать эти представления к схеме, чтобы избежать случайного изменения базовой таблицы без изменения представления (подумайте о бедном следующем парне).
When a table column is to be renamed
По моему опыту, такое случается редко. Добавление столбцов, удаление столбцов, изменение индексов и изменение типов данных - обычные сценарии изменения таблицы, которые вы будете запускать.
It is easier to implement derived attributes (easier than using computed columns).
Я бы поспорил с этим. В чем разница между помещением вычисления в определение столбца и его помещением в определение представления? Кроме того, вы увидите снижение производительности, если переместите его в представление вместо вычисляемого столбца. Единственное реальное преимущество состоит в том, что изменение вычисление проще в представлении, чем при изменении таблицы (из-за индексов и страниц данных).
You can effectively have aliases for column names.
Это настоящая причина иметь взгляды; псевдонимы таблиц и столбцов, а также объединение нескольких таблиц. Лучшей практикой в моих последних нескольких работах было использование представлений там, где мне нужно было денормализовать данные (поисковые запросы и тому подобное, как вы уже указали).
Как обычно, наиболее правдивый ответ на вопрос администратора баз данных - «это зависит» - от вашей ситуации, набора навыков и т. д. В вашем случае рефакторинг «всего» в любом случае приведет к поломке всех приложений. Если вы правильно исправите базовые таблицы, косвенное обращение, которое вы пытаетесь получить от своих представлений, не потребуется и только удвоит обслуживание вашей схемы для любых будущих изменений. Я бы сказал, пропустите представления оболочки, исправьте таблицы и сохраненные процедуры (которые уже предоставляют достаточно информации, скрытой), и все будет в порядке.
Что касается частоты изменения, добавления и удаления столбцов: очевидно, это зависит от системы. В нашем случае изменения происходят гораздо чаще, чем добавления и удаления.
Хороший момент по поводу UDF в вычисляемых столбцах, я не учел этого. Вы правы, невозможность изменить функцию без изменения сначала вычисляемого столбца будет раздражать. Я по-прежнему считаю, что представления следует создавать только при необходимости, а не как стандарт.
Что касается производных атрибутов: возможно, лучший пример - это когда вычисление производного атрибута включает UDF. С вычисляемым столбцом в таблице любое изменение UDF - настоящая головная боль.