-
Notifications
You must be signed in to change notification settings - Fork 0
Database structure
Simon Rolph edited this page Aug 28, 2024
·
1 revision
-
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.
-
-
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.
-
-
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 referencingusers(id)
, stores the ID of the user. -
email_list_id
: Integer, Foreign Key referencingemail_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.
-
-
Emails Table
- Purpose: To track the emails sent to users.
-
Columns:
-
id
: Integer, Primary Key, Auto-incremented. -
user_id
: Integer, Foreign Key referencingusers(id)
, stores the ID of the user. -
email_list_id
: Integer, Foreign Key referencingemail_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.
-
-
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 referencingemails(id)
, stores the ID of the email. -
user_id
: Integer, Foreign Key referencingusers(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.
-
- 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.