Search code examples
javamysqldatabasedatabase-designdatabase-normalization

Accurate tables for Database normalization

I am thinking on how could I normalize as accurate as possible my tables in my database.

I have to develop a monitoring system where the system should analyse all the outgoing messages(Emails, SMS..) send to customers and their related feedback(status(0,1 or more)).

It means that for 1 outgoing message(With for ex MAILID: 123) I can have multiple different feedbacks(SENT, NOT DELIVERED, DELIVERED, CLICKED, OPENED...) received at different time intervals. For some feedback received I must retrieve the information of what the Email Service provider did in term of "Action". Did they send the email again to the customer?, did they manage to resolve the servers issues if not delivered? etc..

EDIT: I am sorry in fact my MAIL ID columns are all VARCHARS (Java Strings). Long story short, In fact I have to process XML files and unmarshal them to Java Object and persist them into my Mysql dataBase where MAIL ID is in the form of BMM1EP_34022503920_1200180009

To clarify I thought I should have 4 main tables.

Table SentMessages with columns:

  1. MailId VARCHAR primary key(not null)
  2. Form_Name varchar(50)
  3. Language varchar(10)
  4. OutPut_Mode varchar(10)

Table FeedBackMessages with columns:

  1. Id INT AutoIncrement primary key (not null)

  2. MailId VARCHAR

  3. Return_Date DATE

  4. Return_Time DATETIME

    FOREIGN KEY (MailId) REFERENCES SentMessages (MailId)

Table Status with columns:

  1. Id Int AutoIncrement primary key (not null)

  2. MailId VARCHAR

  3. Status VARCHAR(50) FOREIGN KEY (MailId) REFERENCES SentMessages(MailId)

Table Action with columns:

  1. Id Int AutoIncrement primary key(not null)

  2. ActionTaken VARCHAR(100)

  3. MailId VARCHAR FOREIGN KEY (MailId) REFERNCES Status(Id)

Is my design bad? Help will be highly appreciated as I really struggle it is my first time designing a real database for a concrete project. Thanks to all of you!

Solution

  • It helps if you use a semi-formal natural language to define your business domain. As I understand it:

    There are 0..n email messages

    Each email message will have 0..n feedback items

    Each feedback item has 1 status, and may have additional action information.

    If that's accurate, the design would be something like:

    Table SentMessages with columns:

MailId varchar primary key(not null)
Form_Name varchar(50)
Language varchar(10)
OutPut_Mode varchar(10)

Table FeedBackMessages with columns:

Id INT AutoIncrement primary key (not null)
MailId varchar
Return_Date DATE
Return_Time DATETIME
Status  VARCHAR(50)
ActionTaken VARCHAR(100)
FOREIGN KEY (MailId) REFERENCES SentMessages (MailId)