Consolidating data from columns of different tables design problem

Question

I have a 2 tables: a membership table and mailing list table.

[Members] 
Username
Password
EmailAddress
MailingListOptIn [bit]
Planes [bit]
Boats [bit]
Cars [bit]

• The fruits columns are user preferences.

and

[MailingList]
EmailAddress
MailingListOptIn

• A mailinglist'er can opt to receive newsletters but may not necessarily be a member

I would like to start storing preferences for mailinglist'ers. My question is what would be the best way to consolidate these 2 tables data?

I've experimented with the members table storing only member info like username, password etc and a separate profiles table storing the email address and preferences. Both members and mailing list preferences can be stored in this profile table but I can't add an FK constraint cause then I can't add a mailinglist'er.

So right now my options are:

1. Stick with the 2 tables but introduce duplicate "preferences" columns on the mailinglist table.
2. Use the single profiles table and screw referential integrity.

Or maybe there is another better way?

Answer 1

I would do it like this:

i) a "person" table with the email address as key

ii) a "member" table, only members will have a record in this table, linked to "person" by emailaddress (also key in this table)

iii) a "mailingList" table, having a unique id for the mailingList, the description and maybe other fields

iv) a "mailingListSubscriber" table (a relationship) with the email address of the person and the id of the mailing list.

Sorry for adding tables but I think it's the minimal setting for adequate normalization given the requirements.

Answer 2

CREATE TABLE Profiles (
 Username
 Password
 EmailAddress
 MailingListOptIn [bit]
 Planes [bit]
 Boats [bit]
 Cars [bit]
);

CREATE VIEW Members AS
  SELECT * FROM Profiles WHERE Username IS NOT NULL
  WITH CHECK OPTION;

CREATE VIEW MailingList AS 
  SELECT EmailAddress, MailingListOptIn, Planes, Boats, Cars
  FROM Profiles WHERE Username IS NULL
  WITH CHECK OPTION;