ERModel Current version

Diagram

Data requirements

ENTITIES

User
Represents every person using the platform: buyers, sellers, and administrators. It is needed to authenticate users, assign roles, and link their actions (listings, reviews, notifications, ownership of animals). It is modeled as a single entity with a role attribute instead of separate tables for buyers/sellers/admins, because one person can play multiple roles over time.
Candidate keys:

• user_id – surrogate numeric identifier (chosen primary key).
• username – natural key, must be unique, but can change in the future.
• email – also unique, but might change and can be reused if a user is deleted.

user_id is chosen as PK because it is stable, short, and independent of business rules.
Attributes:

• user_id – numeric, required. Auto-generated positive integer; primary key.
• username – text, required. Unique; max length ~30; only letters, digits and some symbols.
• role – text, required. Limited set of values: {ADMIN, USER, GUEST}; implemented as enum.
• fullname – text, required. Free-text full name.
• name – text, required. First name; max length ~50.
• surname – text, required. Last name; max length ~50.
• email – text, required. Must be unique; email format validation.
• created_at – datetime, required. Automatically set when the account is created.

ERModel History