CODEWITHSUNDEEP
sqldatabasedatabase-design
Derek
Derek

Reputation: 8630

Database Model - SQL - Best Approach

I'm looking for help with part of a database design.

I have to Model the Database for a Group of Contacts and a Group of Distribution Lists.

Each Contact can be in many Distribution Lists and each Distribution List can have many Contacts. In a normal Instance, I could use a Junction table to achieve this solution.

But there's one more thing to add. Contacts have the option to receive notifications via two different methods which are SMS or Email.

A Contact can request to be sent notification via either or both methods.

The piece of the problem that I am stuck with, is that a Contact may wish to receive notifications differently depending on the specific distribution list.

So we have a problem like this :--

CONTACT A is in DL-A - Receives Notification via SMS 
CONTACT A is in DL-B - Recieves Notification via Email & SMS.

I'm trying to avoid having more than one entry for a Contact in my Contacts Table, each contact should be unique.

Can Anyone help?

Upvotes: 2

Views: 148

Answers (4)

mmmmmm
mmmmmm

Reputation: 32626

In this case I would add two fields to the junction table SMS and email both boolean and set to true if and only if they wish to receive notification in that matter. This allows the notifications to be set differently for combination list and contact.

Also depending on how you want to deal with removal from lists you could add a constraint on the junction table so that at least one of the two fields is true so that a notification is always sent although say in google groups I do have access to some lists which I have chosen not to get notifications from.

Upvotes: 1

Alberto De Caro
Alberto De Caro

Reputation: 5213

So you can add to fields in the junction table:

ContactsDistributions(ContactId, DistributionId, SMSFlag, EmailFlag)

in order to specify the type of notification choosen by the contact for each distribution.

Upvotes: 2

user15
user15

Reputation: 1054

You can add one more field in junction table which will represent how given contact will receive notification from given distribution list.

Upvotes: 1

Matt Gibson
Matt Gibson

Reputation: 14949

You could use another junction table:

contactid, distributionlistid, messagepreference

Messagepreference can be email or SMS. Two rows if they want both. New messaging types can be added with no changes to the DB. To be safe, use constants in your code to represent the values you will put into the columns.

Or, add sendemail and sendsms columns to the original junction table, but this has the drawback that you have to change DB structure if you introduce a new messaging type.

Upvotes: 4

Related Questions