0

First question for me on the DBA SA site!

This is for an Java email notification app using MySQL for backend.

I have a small number of groups in our organization, [Marketing, Commerce, Infrastructure, Design]. They all are looking to utilize a small number of email templates for this app. Currently all of the groups utilize all of the templates [AAR Summary, Awareness, Daily Status, Hourly Deploy Update].

My question is what is a very simple, scalable way to design these two? My first thought was to have two tables:

Division
    - DivisionID
    - DivisionName

Template
    - TemplateID
    - TemplateName
    - AssociatedDivision

However, I don't want to have duplicate templates for different divisions. In the same regard, a comma delimited list would help but I have no clue how I could query for all divisions and all templates each division has available.

There is currently a solution in place that is less than ideal. This solution is driving what people can and can not subscribe to.

Essentially, user x can subscribe or not to each template for each division.

EXAMPLE:

              AAR Summary    |    Awareness    |    Daily Status    |    Hourly Deploy
            -------------------------------------------------------------------------------
Marketing |       X                   X                  
Commerce  |       X                   X                  X                     X
Infra.    |                           X
Design    |                           X                  X

Help, if you need more info just let me know?

1

I think this is a many to many relation ship, and the suggested way for that is to have a table for the relation. i.e.:

Division
- DivisionID
- DivisionName

Template
- TemplateID
- TemplateName

division_template
- DivisionID
- TemplateID
| improve this answer | |
  • So for a "subscription" idea, could I just add a UserID to the division_template (name aside) to easily create a subscription table on the page? – Nicholas Anderson Nov 10 '14 at 2:00
  • Nevermind, that did it! Then I can just select all where userid is the user in question. – Nicholas Anderson Nov 10 '14 at 2:19

Not the answer you're looking for? Browse other questions tagged or ask your own question.