vendredi 27 mars 2015

What will be the Better database design for following table?


I am designing database schema for bulk mail system.


campaign table is used to store the campaign details.



Table Campaign
-------------------------------------------------------------------------------
id | subject | message | status | sent | opened | bounced |senttime | User_id
-------------------------------------------------------------------------------


campaign_log table will used to store the every recipents status like he opens mail or mail bounces for this email or sent etc.



Table Campaign_log
--------------------------------
id | campaign_id | email |status
---------------------------------


My questions is



  1. should I make the columns in table campaign for storing the count of sent,opened and bounced mails

  2. Should I make the another table for storing the statistical data like sent,opend and bounced mail ? can it improve a performance.

  3. I like to explain the second question to describe my doubt (may be it silly stupid idea) I am explaining my doubt with above example. In campaign table id,subject message fields are filled by the user while sent, opened and bounced columns are fills after sending the campaign. Is there more unused column at the inserting or updating affects the performance I mean to say when inserting the campaign, the sent opened and bounced are just like unused we are not filiing them

  4. I read about OLTP AND OLAP databases. But I am confuse about my application nature. because there is because my database is going to store the thousands of row in tables such as mailing list also there is more updates operation will be performs and it's also have the reporting, analysis system.


and when updatingthe count for each column sent opened and bounced other columns are unused.


I am sorry I think it's very silly but I am beginnig to database design and have the doubt and I want to clear it.





Aucun commentaire:

Enregistrer un commentaire