Mysql Database Design – identifying required patterns

database-designdesign-patternMySQL

I'm trying to figure out which pattern (if any) might describe the problem I'm facing when designing the database for a club membership management system. I require help in identifying the pattern (if one exists) or some pointers of what considerations I need to make in order to make some headway.

Members are a main entity, with some information such as contact details on them.
Members can have central functions at the club, ie be the treasurer or commodore etc.

In order to model the club's top level hierarchy, I would have the ID from Members, an enumeration table "BoardMemberTypes" and a "BoardMembersType" table linking the two.

However, the Club also has sub-groups, each with their own hierarchy structure.

Every Member is part of at least one of the Subgroups, with the subgroup Role "member", and the membership type such as "active" or "passive".

Each of these structures have a few identical and a few specific roles. As an example:

Subgroup A has chair, vice chair, treasurer from the identical set, and "Subgroup A Technology Chair" from a list only applicable to Subgroup A
Subgroup B has a chair, a treasurer, and a "Specific Subgroup B 1" and "Specific Subgroup B 2" role
Subgoup C might have only a chair and a "Specific Subgroup C" role.

The Subgroups are the complicated bit:

Enumeration Table "Subgroup" (ID, Name)
Enumeration Table "MembershipType" (ID, Name)
Enumeration Table "MembershipFunction" (ID, Name)

First hurdle: When presenting the admin interface, I'd need to constrain MembershipFunction depending on the subgroup.
I guess this could be achieved through a link table:

 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

In theory, I could now create a table "MembersSubgroupMembershipFunctions" with MembersID, SubgroupMembershipFunctionsId

At this point, I'm still lacking the abilty to store the MembershipType (active, passive) which only really applies to the "member" entry.
The end result I need would be something like this maybe

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(?)

Any help, pointer or idea towards improving this design is greatly appreciated.

Best Answer

I don't know if there is a design pattern beyond "normalize properly", but I don't think you're too far off from a workable solution.

Given your description I don't see a need for a Subgroup entity - we can just have Groups and denote one of those as "The Board".

For specific GroupFunctions, we'll consider those to be a special case for GroupMembers, i.e. once someone is assigned to a group they can be assigned Chair/Vice Chair/Special Assistant to the Jr Vice Chair/etc. Each GroupFunction can be assigned to at most one GroupMember.

As for active/passive/etc., it's entirely possible to have one level for the Member and different levels for each Group, but you might need some transaction logic if a "passive" member cannot be an "active" member of a group. Alternately, you could derive the MembershipType for each Member based on the groups they are members of.

I'll leave the datatypes and full DDL to you (including audit tables), but this should work:

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)
);

Pictures are usually easier to understand (if you are unfamiliar with IDEF1X, read this):

Related Solutions

MySQL Database Design – Efficient Ordering Techniques

You can change your post_order column to a float and update the value with a calculation using the post_order values from the posts you want to end up between.

Example:

PostID, PostOrder
1       1
2       2
3       3

If you want to move PostID = 3 to be sorted between 1 and 2 it would be

PostID, PostOrder
1       1
3       1.5
2       2

Here is a SQL Fiddle with some code that moves the posts using a stored procedure where you pass in the PostID of the post to be moved and PostID of the post you want to end up behind.

SQL Fiddle

MySQL 5.5.30 Schema Setup:

create table Post
(
  PostID int primary key,
  PostOrder float unsigned not null unique,
  check (PostOrder > 0)
);

insert into Post(PostID,  PostOrder) values (1, 1);
insert into Post(PostID,  PostOrder) values (2, 2);
insert into Post(PostID,  PostOrder) values (3, 3);
insert into Post(PostID,  PostOrder) values (4, 4);
insert into Post(PostID,  PostOrder) values (5, 5);
insert into Post(PostID,  PostOrder) values (6, 6);
insert into Post(PostID,  PostOrder) values (7, 7);
insert into Post(PostID,  PostOrder) values (8, 8);
insert into Post(PostID,  PostOrder) values (9, 9);
insert into Post(PostID,  PostOrder) values (10, 10);

//

create procedure MovePost(MovePostID int, AfterPostID int)
begin

  declare AfterPostOrder float;
  declare NextPostOrder float; 


  set AfterPostOrder = (select PostOrder 
                        from Post
                        where PostID = AfterPostID);

  if AfterPostOrder is null then
    -- Move first
    set AfterPostOrder = 0;
  end if;

  set NextPostOrder = (select min(PostOrder) 
                       from Post 
                       where PostOrder > AfterPostOrder);

  if NextPostOrder is null then
    -- Move last
    set NextPostOrder = (select max(PostOrder) + 2
                         from Post);
  end if;


  update Post
  set PostOrder = (AfterPostOrder + NextPostOrder) / 2
  where PostID = MovePostID;


end

Query 1:

call MovePost (7, 3);    -- Move 7 after 3
call MovePost (8, 3);    -- Move 8 after 3
call MovePost (9, null); -- Move 9 first
call MovePost (2, 10);   -- Move 2 after 10

select * 
from Post
order by PostOrder

Results:

| POSTID | POSTORDER |
----------------------
|      9 |       0.5 |
|      1 |         1 |
|      3 |         3 |
|      8 |      3.25 |
|      7 |       3.5 |
|      4 |         4 |
|      5 |         5 |
|      6 |         6 |
|     10 |        10 |
|      2 |        11 |

Sql-server – Database Design guidance required

I'm not sure either of these are really great options. Here's my thoughts:

pk|A ,pk|B ,PK|C ,PK|D ----> JOIN AND INSERT INTO TABLE T(pk,a,b,c,d)

With this scenario you have redundant data. Unless your individual PK tables are just staging tables, I think you could run into some serious issues here, especially with different consumers pulling data from different tables. I wouldn't chance that whatsoever with this design.

Not to mention that if PK:A, PK:B, PK:C, and PK:D are all 1:1 relationships, I don't see any normalization benefit out of this either.

T (PK,A,B,C,D) DATA SOMETHING like (1,a1,NULL,NULL,NULL) run update statement for all columns B,C,D sequentially and get all the data T(1,a1,b1,c1,d1)

I'm not sure I like this design either, although I'd put it at a higher preference then the former. The reason behind this is because what happens if business dictates that columns A, B, C, and/or D really aren't NULLable. Now you can run into a big problem of data conflicting with business constraints.

I think the real solution here may be to take a step back and analyze your data modification. Instead of having a step-based data insert, is there no way to do a single insert of all the required and necessary data into this table? In the application, retrieve all of the required data before each item's insert. That way you won't be defying any business rules, having redundant data, or additional operation overhead of the designs above.

In other words, if data columns A, B, and C are required, then you shouldn't be pushing that data out to this table until you have it. Without all of those required columns you wouldn't have compliant data, and it shouldn't live in that table until it is. And if D is nullable and optional data, then sure that's fine if it doesn't appear until a later time with a different DML operation.

Best Answer

Related Solutions

MySQL Database Design – Efficient Ordering Techniques

Sql-server – Database Design guidance required

Related Question