Дизайн базы данных MySQL - определение необходимых шаблонов
Я пытаюсь выяснить, какой шаблон (если таковой имеется) может описать проблему, с которой я сталкиваюсь при разработке базы данных для системы управления членством в клубе. Мне нужна помощь в идентификации шаблона (если он существует) или некоторые указания на то, какие соображения мне нужно сделать, чтобы добиться некоторого прогресса.
Члены - это основная сущность, имеющая некоторую информацию, например, контактные данные. Члены могут выполнять центральные функции в клубе, то есть быть казначеем, коммодором и т. Д.
Чтобы смоделировать иерархию верхнего уровня клуба, мне нужно было бы иметь идентификатор от членов, таблицу перечисления BoardMemberTypes и таблицу BoardMembersType, связывающую их.
Однако в Клубе также есть подгруппы, каждая со своей иерархической структурой.
Каждый член является частью хотя бы одной из подгрупп с ролью подгруппы «член» и типом членства, например, «активный» или «пассивный».
У каждой из этих структур есть несколько идентичных и несколько конкретных ролей. Например:
- В подгруппе A есть председатель, заместитель председателя, казначей из идентичного набора, а «технологический председатель подгруппы A» из списка, применимого только к подгруппе A.
- В подгруппе B есть председатель, казначей и роль «Особая подгруппа B 1» и «Особая подгруппа B 2».
- Подгруппа C может иметь только председателя и роль «Особой подгруппы C».
Подгруппы - это сложный бит:
Enumeration Table "Subgroup" (ID, Name)
Enumeration Table "MembershipType" (ID, Name)
Enumeration Table "MembershipFunction" (ID, Name)
Первое препятствие: при представлении интерфейса администратора мне нужно было ограничить функцию MembershipFunction в зависимости от подгруппы. Думаю, этого можно было бы достичь с помощью таблицы ссылок:
SubgroupMembershipFunctions (Id, SubgroupId, MembershipFunctionId)
e.g. (Name output after applying joins obviously)
1 Subgroup A Member
2 Subgroup A Chairman
3 Subgroup A Subgroup A Technology Chair
4 Subgroup B Member
5 Subgroup B Chairman
6 Subgroup B Specific Subgroup B 1
Теоретически теперь я мог бы создать таблицу «MembersSubgroupMembershipFunctions» с помощью MembersID, SubgroupMembershipFunctionsId
На данный момент мне все еще не хватает возможности хранить MembershipType (активный, пассивный), который действительно применяется только к записи «член». Конечный результат, который мне нужен, будет примерно таким: возможно
User Subgroup Type Status
Justin Case A member active
Justin Case A chairman null(?)
Justin Case B member passive
Justin Case B B 1 null(?)
Joe Bloggs A member active
Jane Doe B member active
Jane Doe C member passive
Jane Doe C vicechair null(?)
Любая помощь, указатель или идея по улучшению этого дизайна приветствуются.
Ответы
Я не знаю, есть ли какой-либо шаблон проектирования, помимо «правильной нормализации», но я не думаю, что вы слишком далеки от работоспособного решения.
Учитывая ваше описание, я не вижу необходимости в Subgroup
сущности - мы можем просто иметь Groups
и обозначить одну из них как «Совет».
В частности GroupFunctions
, мы будем рассматривать их как особый случай GroupMembers
, т.е. когда кто-то назначен в группу, он может быть назначен председателем / заместителем председателя / специальным помощником младшего вице-председателя и т. Д. Каждому GroupFunction
можно назначить не более одного GroupMember
.
Что касается активного / пассивного / и т. Д., Вполне возможно иметь один уровень для Member
и разные уровни для каждого Group
, но вам может потребоваться некоторая логика транзакции, если «пассивный» член не может быть «активным» членом группы. В качестве альтернативы вы можете получить MembershipType
для каждого Member
на основе групп, в которые они входят.
Я оставлю вам типы данных и полный DDL (включая таблицы аудита), но это должно работать:
CREATE TABLE MembershipType
(
MembershipTypeCd
,Name
,CONSTRAINT PK_MembershipType PRIMARY KEY (MembershipTypeCd)
,CONSTRAINT AK_MembershipType UNIQUE (Name)
);
CREATE TABLE Member
(
MemberId
,MembershipTypeCd
,CONSTRAINT FK_Member_Has_MembershipType FOREIGN KEY (MembershipTypeCd) REFERENCES MembershipType (MembershipTypeCd)
,CONSTRAINT PK_Member PRIMARY KEY (MemberId)
);
CREATE TABLE MemberFunction
(
FunctionShortName
,Name
,CONSTRAINT PK_MemberFunction PRIMARY KEY (FunctionShortName)
,CONSTRAINT AK_MemberFunction UNIQUE (Name)
);
CREATE TABLE Group /* A reserved keyword in most DBMS - may make sense to rename */
(
GroupId
,Name
,CONSTRAINT PK_Group PRIMARY KEY (GroupId)
,CONSTRAINT AK_Group UNIQUE (Name)
);
CREATE TABLE GroupMember
(
GroupId
,MemberId
,GroupMembershipTypeCd
,CONSTRAINT FK_GroupMember_Member_Of_Group FOREIGN KEY (GroupId) REFERENCES Group (GroupId)
,CONSTRAINT FK_GroupMember_Is_Member FOREIGN KEY (MemberId) REFERENCES Member (MemberId)
,CONSTRAINT FK_GroupMember_Has_MembershipType FOREIGN KEY (GroupMembershipTypeCd) REFERENCES MembershipType (MembershipTypeCd)
,CONSTRAINT PK_GroupMember PRIMARY KEY (GroupId, MemberId)
,CONSTRAINT AK_GroupMember UNIQUE (MemberId, GroupId)
)
;
CREATE TABLE GroupFunction
(
GroupId
,FunctionShortName
,CONSTRAINT FK_GroupFunction_Available_For_Group FOREIGN KEY (GroupId) REFERENCES Group (GroupId)
,CONSTRAINT FK_GroupFunction_Is_MemberFunction FOREIGN KEY (FunctionShortName) REFERENCES MemberFunction (FunctionShortName)
,CONSTRAINT PK_GroupFunction PRIMARY KEY (GroupId, FunctionShortName)
);
CREATE TABLE GroupFunctionAssignment
(
GroupId
,FunctionShortName
,MemberId
,CONSTRAINT FK_GroupFunctionAssignment_Assigned_To_GroupMember FOREIGN KEY (GroupId, MemberId) REFERENCES GroupMember (GroupId, MemberId)
,CONSTRAINT FK_GroupFunctionAssignment_Assigment_Of_GroupFunction FOREIGN KEY (GroupId, FunctionShortName) REFERENCES GroupFunction (GroupId, FunctionShortName)
,CONSTRAINT PK_GroupFunctionAssignment PRIMARY KEY (GroupId, FunctionShortName)
);
Картинки обычно легче понять (если вы не знакомы с IDEF1X, прочтите это ):
