Database structure

Tables and Schema

1. Users Table

   • Purpose: To store user information.
   • Columns:
     • id: Integer, Primary Key, Auto-incremented.
     • name: Text, stores the user's name.
     • email: Text, stores the user's email.
     • date_created: Timestamp, stores the date and time the user was created, defaulting to the current timestamp.

2. Email Lists Table

   • Purpose: To store different email lists.
   • Columns:
     • id: Integer, Primary Key, Auto-incremented.
     • email_list_name: Text, stores the name of the email list.

3. User Subscriptions Table

   • Purpose: To link users to email lists, representing their subscriptions.
   • Columns:
     • id: Integer, Primary Key, Auto-incremented.
     • user_id: Integer, Foreign Key referencing users(id), stores the ID of the user.
     • email_list_id: Integer, Foreign Key referencing email_lists(id), stores the ID of the email list.
     • date_subscribed: Timestamp, stores the date and time the user subscribed, defaulting to the current timestamp.

4. Emails Table

   • Purpose: To track the emails sent to users.
   • Columns:
     • id: Integer, Primary Key, Auto-incremented.
     • user_id: Integer, Foreign Key referencing users(id), stores the ID of the user.
     • email_list_id: Integer, Foreign Key referencing email_lists(id), stores the ID of the email list.
     • date_sent: Timestamp, stores the date and time the email was sent, defaulting to the current timestamp.

5. Email Feedback Table

   • Purpose: To store feedback provided by users for specific emails.
   • Columns:
     • id: Integer, Primary Key, Auto-incremented.
     • email_id: Integer, Foreign Key referencing emails(id), stores the ID of the email.
     • user_id: Integer, Foreign Key referencing users(id), stores the ID of the user.
     • rating: Integer, stores the user's rating of the email.
     • comment: Text, stores the user's comment on the email.

Relationships Between Tables

• Users and Email Lists are connected through the User Subscriptions table, forming a many-to-many relationship.
• Users and Emails are connected through the Emails table, where each record represents an email sent to a user.
• Emails and Email Feedback are connected through the Email Feedback table, which captures user feedback on specific emails.