Database tables/structure (two tables)

Jaeger

Newbish question here I'm sure; feel free to move as appropriate.

I'm trying to set something up in work here to remove a current dependency on copy and paste macros, and having problems visualising the database structure.

The basic idea is to provide distribution lists (rather: lists of email addresses, not Exchange distribution lists). Ideally I would like a table with separate rows/entries for every user, and a separate table with again separate rows/entries for every distribution list. I could hack together a single basic table with columns for every list, and rows for every user, but I'd like the option to be able to expand/scale in the future, and I don't think that approach would work long term.

What I don't understand is how best to structure the two tables, and how to relate the two. I know there is an elegant way of setting it up, but I'm just not up to it yet. Can anyone point me in the right direction?

I know there are prebuilt apps for mailing/distribution lists but at a glance, they seem like major overkill for what I'm trying to achieve at the moment. I'd rather understand the concept and then apply it now so I can reuse the same concept later on.

Tom Dunne

A basic, inelegant solution would be to have three tables:

Table 1: Distribution_lists
Dis_lis_ID (PK)
Dis_lis_Name

Table 2: Composite
Dis_lis_ID (PFK)
Name_id (PFK)

Table 3: Names
Name_id (PK)
Email_addr

croo

A basic, inelegant solution would be to have three tables:

I don't know about that Tom.

many emails on a distribution list
and
an email on many distribution list

So, your classic many-2-many relationship. And I don't think I have ever seen a solution that did not include a join table in between! So I think your suggestion is the classic solution.

E39MSport

croo wrote: »

I don't about that Tom.

many emails on a distribution list
and
an email on many distribution list

So, your classic many-2-many relationship. And I don't think I have ever seen a solution that did not include a join table in between! So I think your suggestion is the classic solution.

Me too, thats what I had in mind before reading the solution. Should work no problem. Inelegant would describe me well though
edit: nicely explained too Tom imo

mewso

Tom Dunne wrote: »

A basic, inelegant solution would be to have three tables:

Table 1: Distribution_lists
Dis_lis_ID (PK)
Dis_lis_Name

Table 2: Composite
Dis_lis_ID (PFK)
Name_id (PFK)

Table 3: Names
Name_id (PK)
Email_addr

It's the right way to go. I suppose we all have our naming strategies but I do like to make things simple and readable.

DistributionNames
Id
EmailAddress

DistributionLists
Id
ListName

DistributionListNames
ListId
NameId

Jaeger

Thanks for the help folks. I've never previously dealt with Foreign keys, so this is new territory.

Am I right in saying that the relationship data will be handled completely in the composite table? When I add either a new user or new distribution list, I will also need to add any relationship information into the composite table? Should there be any other columns in the composite perhaps to explain the relationship, or is that wasting effort?

croo

Jaeger wrote: »

Am I right in saying that the relationship data will be handled completely in the composite table? When I add either a new user or new distribution list, I will also need to add any relationship information into the composite table?

No, when you add a user you'd create a record in the user table.
When you add a distribution you'd create a record in the distribution list table.

Only when you add a user to a distribution list would you add a record to the "composite" table. When you remove the user from the distribution list, you'd remove the composite record. When you remove a user or a distribution list you'd remove all related composite records as required.

Jaeger wrote: »

Should there be any other columns in the composite perhaps to explain the relationship, or is that wasting effort?

There are no other columns required. A couple of indexes perhaps to ensure it's efficient to find all users on a distribution list and all distributions for a given user.

Tom Dunne

Jaeger wrote: »

Thanks for the help folks. I've never previously dealt with Foreign keys, so this is new territory.

Am I right in saying that the relationship data will be handled completely in the composite table? When I add either a new user or new distribution list, I will also need to add any relationship information into the composite table? Should there be any other columns in the composite perhaps to explain the relationship, or is that wasting effort?

Think of things as independent groups of data.

Can you have an e-mail address without it being in a distribution list? Of course you can.

Can you have a distribution list name without it containing any e-mail addresses? Yup, you sure can.

So, you can have two independent lists, one in each table. It is only when you want to associate a distribution list name with a list of e-mail addresses that you need to populate the composite table.

Can you have an entry in the composite table that does not exist in either of the other two tables? (e.g. can you have an e-mail address in the Composite table that does not exist in the Names table?). No, of course you can't.

So, to answer your question (finally) yes, the composite table handles the relationship between list name and e-mail addresses.