Email and Partner table Design

[drunkenneo]

It Seems Simple but my mind is hovering in lot of confusion



I hav a table

Partner
-------
ID PK
name
email_Id (FK)
Subject
Body


and
Email
-------
email_Id PK
emailaddress
type (1/2)
Desc

I have email which are in To if type in 1 and cc if type is 2. Could not be able to figure if this normalisation is Correct

Any Idea as in my case A partner have set of to/Cc email in it. Can anyone suggest a better designing.

Thanks in Advance

[June7]

I think you have the PK/FK backwards. Seems the partner ID should be FK in Email.

It is a balancing act between normalization and ease of data entry/edit.

Splitting the data into two tables is normalization but is it really necessary? If every Partner will have two email addresses (and only 2 - 1 for To and 1 for Cc) and this will never change, then could just have 2 fields in Partner table. The separate table would definitely be required if you want to allow any number of emails for each partner.

[drunkenneo]

There can be case email id can change from to to cc, and also possiblity two partners can have same email id in cc and to also.

Please can ot b ppssible to see in table format what u have 8n mind?

[June7]

What you have now allows a partner to have only one email. If they need to be associated with two emails then options are 2 fields in Partner table holding the email addresses or a separate Email table with two records for each partner.

If the same email will be both To and Cc then I suppose it will have to be in both fields or both records. If the email can change types, then in Partner table fields you have to edit the entries. If you use the Email table then just change the Type code of both records. It's irrelevant if partners have the exact same email (how could that happen?).

I am not advocating one way or the other, only you can decide which works best for you.

Or maybe I don't understand what the Partner table is for. I just noticed the Subject and Body fields. Why would email subject and body be different for each partner?

[drunkenneo]

What you have now allows a partner to have only one email. If they need to be associated with two emails then options are 2 fields in Partner table holding the email addresses or a separate Email table with two records for each partner.

If the same email will be both To and Cc then I suppose it will have to be in both fields or both records. If the email can change types, then in Partner table fields you have to edit the entries. If you use the Email table then just change the Type code of both records. It's irrelevant if partners have the exact same email (how could that happen?).

I am not advocating one way or the other, only you can decide which works best for you.

Or maybe I don't understand what the Partner table is for. I just noticed the Subject and Body fields. Why would email subject and body be different for each partner?

Subject and body, its different for all different partners, have different subject lines it (Totally different contents) and body. Now i will denormalize it and keep in column of to and cc.

[June7]

Actually, there is a third arrangement you could consider.

Partners
ID PK
name
email_TO (FK)
email_CC (FK)
Subject
Body

Email
email_Id PK
emailaddress

Email table would be a list of ALL emails and they would be unique, no duplicates. Then in Partners table, email would be selected from combobox list, and the email_ID would be saved. This approach might be preferable if there is a LOT of repetition of emails in the Partners table, if a lot of the partners use the same addresses and/or a lot of the emails are both To and Cc. If those circumstances are rare, then don't go this route. With this approach, a query would include the Email table twice, joining to each of the FK fields.