Rails中用户、页面所有者与访客的关联数据库设计求助
Alright, let's work through a normalized database schema for your page ownership and visitor invitation system. This structure is flexible, scalable, and covers all your core requirements:
Core Table Structure
1. users Table
Stores all registered users in your system.
| Column Name | Data Type | Description |
|---|---|---|
user_id | INT (PK, AUTO_INCREMENT) | Unique identifier for each user (primary key) |
username | VARCHAR(50) | Unique display name/username (enforce uniqueness) |
email | VARCHAR(100) | User's email address (used for invitations, enforce uniqueness) |
password_hash | VARCHAR(255) | Hashed password (never store plain text!) |
created_at | DATETIME | Timestamp when the user account was created |
updated_at | DATETIME | Timestamp when the user account was last updated |
2. pages Table
Stores all website pages, with a direct link to their owner.
| Column Name | Data Type | Description |
|---|---|---|
page_id | INT (PK, AUTO_INCREMENT) | Unique identifier for each page |
page_name | VARCHAR(100) | Name/title of the page |
page_slug | VARCHAR(100) | URL-friendly version of the page name (unique, used for routing) |
owner_id | INT (FK) | Links to users.user_id - identifies the page's creator/owner |
created_at | DATETIME | Timestamp when the page was created |
updated_at | DATETIME | Timestamp when the page details were last updated |
Key Constraint: Add a foreign key on owner_id referencing users.user_id with ON DELETE CASCADE (adjust to ON DELETE SET NULL if you want to retain pages without an owner instead).
3. page_invitations Table
Tracks all sent invitations for users to join a page. This handles the pending state before a user accepts or rejects the invite.
| Column Name | Data Type | Description |
|---|---|---|
invitation_id | INT (PK, AUTO_INCREMENT) | Unique identifier for each invitation |
page_id | INT (FK) | Links to pages.page_id - the page the invitation is for |
sender_id | INT (FK) | Links to users.user_id - the user who sent the invitation (usually the page owner, but you can extend this to allow other members later if needed) |
recipient_id | INT (FK) | Links to users.user_id - the user being invited |
status | ENUM('pending', 'accepted', 'rejected', 'expired') | Current state of the invitation |
invited_at | DATETIME | Timestamp when the invitation was sent |
responded_at | DATETIME | Timestamp when the recipient responded (null until they accept/reject) |
Unique Constraint: Add a composite unique key on (page_id, recipient_id) to prevent duplicate invitations to the same user for the same page.
Foreign Keys:
page_idreferencespages.page_id(ON DELETE CASCADE: if the page is deleted, delete all related invitations)sender_idandrecipient_idreferenceusers.user_id(ON DELETE CASCADE: if a user is deleted, remove their sent/received invitations)
4. page_members Table
Stores all active members of a page, including the owner and accepted visitors. Using a separate table makes it easy to query who has access to a page at any time.
| Column Name | Data Type | Description |
|---|---|---|
member_id | INT (PK, AUTO_INCREMENT) | Unique identifier for each page membership |
page_id | INT (FK) | Links to pages.page_id |
user_id | INT (FK) | Links to users.user_id |
role | ENUM('owner', 'visitor') | The user's role on the page (owner has full control, visitor has read/limited access) |
joined_at | DATETIME | Timestamp when the user became a member |
Unique Constraint: Composite unique key on (page_id, user_id) to ensure a user can't be added to the same page multiple times.
Foreign Keys:
page_idreferencespages.page_id(ON DELETE CASCADE: remove members if the page is deleted)user_idreferencesusers.user_id(ON DELETE CASCADE: remove memberships if the user is deleted)
Business Logic Flow
Here's how the tables work together in practice:
- When a user creates a page:
- Insert a new record into
pageswithowner_idset to the creator'suser_id. - Automatically insert a record into
page_membersfor the creator withrole = 'owner'.
- Insert a new record into
- When an owner sends an invitation:
- Insert a new record into
page_invitationswithstatus = 'pending'.
- Insert a new record into
- When the recipient accepts the invitation:
- Update the corresponding
page_invitationsrecord tostatus = 'accepted'and setresponded_at. - Insert a new record into
page_memberswithrole = 'visitor'.
- Update the corresponding
- If the recipient rejects the invitation:
- Update the
page_invitationsrecord tostatus = 'rejected'and setresponded_at.
- Update the
Index Recommendations
To optimize common queries (like fetching all members of a page, or pending invitations for a user):
- Add indexes on
pages.owner_id - Add indexes on
page_invitations.recipient_idandpage_invitations.page_id - Add indexes on
page_members.page_idandpage_members.user_id
This schema keeps things organized, avoids data duplication, and gives you room to extend functionality later (like adding different visitor roles, or allowing owners to revoke access).
内容的提问来源于stack exchange，提问作者Sourabh Banka
