Дизайн базы данных - таблицы множественного поиска/перечисления или один большой стол?

У меня есть много таблиц, которые используют ссылки Lookup/Enum для большинства значений столбцов. Например:
Таблица персонажей - PersonID | RaceCode | Волосы для волос | Волосы для волос | TeethConditionCode
Таблица местоположений - LocationID | SizeCode | ExteriorColorCode | ConditionCode
Такие вещи, как Race, Size, Color, Condition и т.д., Будут просто ссылками на внешний ключ для таблицы поиска кода. Эта кодовая таблица имеет другие поля, но не важна для моего вопроса. База данных предназначена для приложения SaaS, что означает, что каждый клиент может иметь свой собственный список цветов, расов, условий и т.д. Существуют некоторые коды, которые были бы статическими, которые клиенты не могли изменить.

Лучше ли иметь 1 кодовую таблицу или 2 типа кодовых таблиц (DynamicCodeTable для определенных клиентов и StaticCodeTable для тех, которые меняют), или мне нужно иметь таблицу для каждого типа кода (RaceCodeTable, HairColorTable, Condition и т.д.)?

То, что меня больше всего волнует, - это все соединения sql. В таблице Person, с которой я работаю, есть 20+ этих атрибутов кода. Есть ли разница в производительности при присоединении к 20 различным таблицам VS, присоединяющимся к одной и той же таблице 20 раз? Наличие нескольких таблиц означает, что каждая таблица будет меньше, а поиск "должен" займет меньше времени. Но иметь одну таблицу тоже можно было бы быстро. Любые предложения?

Ответы

Ответ 1

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

Ответ 2

Этот раздел обсуждался подробно в течение последних пятнадцати лет по теме "Одна таблица True Lookup" (сокращенно OTLT). Преимущества такого подхода выходят на новичку базы данных. Недостатки возникают со временем. См. Эти ссылки для недостатков OTLT:

Или search для OTLT, чтобы найти больше обсуждений.

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

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

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

(Изменено для изменения "интеллектуального анализа данных" на "археологию данных" )

Ответ 3

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

Так что я узнал?

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

Ответ 4

Там есть потенциальная разница в производительности.

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

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