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?

closed as off-topic by Mark Storey-Smith, RolandoMySQLDBA, Michael - sqlbot, Max Vernon, RLF Nov 12 '14 at 14:08

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Mark Storey-Smith, RolandoMySQLDBA, Michael - sqlbot, Max Vernon, RLF
If this question can be reworded to fit the rules in the help center, please edit the question.

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
  • 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.