0

I'm struggling on how to map out the model for a database I'm looking to build. I'll be using SQL2LINQ in C# once I have the database modeled correctly.

The first part that I'm looking at is designing the SQL Tables for distribution lists.

These DL seem quite complex as the following rules apply :

  1. There are three different types of DLs (Used for specific events).
  2. Each type of DL will contain multiple DLs (can be more than 10).
  3. A person can be in all three different types of DL.
  4. A person can be in multiple DLs of the same type.

For the person, I will be holding information such as :

  1. Forename
  2. Surname
  3. Contact Number
  4. Email Address

What I dont like is the idea of duplicating information, having the same person in the database multiple times; that seems not so clean.

Any help would be greatly appreciated, I can't quite wrap my head around the best way to do this.

Improve this question
1
  • When DL contains a DL, must they be of the same type? Commented Jun 20, 2012 at 20:52

2 Answers 2

Reset to default
1

It sounds as if you just want a many-to-many relationship between people and distribution lists, which you can model with a junction table:

Person

  • Id
  • Forename
  • Etc

DistributionListEntries

  • Id
  • DistributionListId
  • PersonId

DistributionList

  • Id
  • Type

You could either store the Type directly in the distribution list table, or have this as a foreign key to a DistributionListTypes table with additional information about the distribution list type.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

You need to learn about foreign keys and parent/child relationships.

I'm not sure that I'd design it exactly as you're describing it. You can have a separate table for each distribution list, or you can have a single one with a column to say which event type it belongs to.

I don't see why a distribution list needs to be recursive, but you can have a foreign key relationship to yourself. The queries will be harder to write that way, but it can be done by a person with sufficient skill. Google for "recursion SQL."

Have a PERSON table, a DISTRIBUTION_LIST table, and a JOIN table between them. Sounds like there's a many to many relationship between PERSON and DISTRIBUTION_LIST to me.

Improve this answer

4 Comments

Thanks, Thats helped alot. The way this is setup, isnt something that I have designed, its what I've inherited. Its all done in Excel Spreadsheets currently, I'm looking to bring it on, and upto date.
I dont understand these JOIN Tables :-(
I think i know where this is leading to now, You use teh Join table to associate the 2 tables, so in the Join Table I add a record with a Person ID and a DistributionListID, and that forms the relationship between teh two tables. I think i have this down now. My only problem is that mu ID fields in the join tbale are both samm Ints.. so when editing it, Its a matter of knowing the correct int value for each distribution List or Person! :-(
Not necessarily. For those occasions when you know one or the other ("Give me all the lists for this person" or "Tell me all the people who belong to this list") this works fine. For other situations you'll use a WHERE clause on the parent table (e.g. "Tell me all the people on a list that have red hair.")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.