Дизайн базы данных MySQL - определение необходимых шаблонов

Aug 17 2020

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

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

Чтобы смоделировать иерархию верхнего уровня клуба, мне нужно было бы иметь идентификатор от членов, таблицу перечисления 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(?)

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

Ответы

1 bbaird Aug 17 2020 at 22:54

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

Учитывая ваше описание, я не вижу необходимости в 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, прочтите это ):