CODEWITHSUNDEEP
databasedatabase-designnormalization
zulkamal
zulkamal

Reputation: 578

Consolidating data from columns of different tables design problem

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

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

and

[MailingList]
EmailAddress
MailingListOptIn

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?

Upvotes: 1

Views: 154

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562310

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;

Upvotes: 1

tekBlues
tekBlues

Reputation: 5793

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.

Upvotes: 0

Related Questions