I'm designing a database for a platform where users can post different types of content.
Database Engine: SQL Server
The system includes:
Proposals
Experiences
Events
##Current Schema Design##
Proposals
- id (primary key)
- user_id (references Users)
- title
- description
- location
- date
- max_participants
- confirmed_participants
- created_at
Experiences
- id (primary key)
- user_id (references Users)
- title
- description
- location
- happened_at
- created_at
Events
- id (primary key)
- user_id (references Users)
- title
- description
- location
- event_date
- ticket_price
- ticket_url
- created_at
Data Volume & Query Patterns
Frequent queries:
- Retrieve all posts of a specific type (e.g., all events).
- Retrieve all posts across all types (for a global feed).
- Fetch a single post by ID, regardless of type.
- Filter by date, location, category, and user engagement (likes, comments).
Since all post types share common fields (id, user_id, title, description, location, created_at), I considered a Table Per Type (TPT) approach to avoid duplication.
Publication (Base Table)
- id (primary key)
- user_id (references Users)
- title
- description
- location
- created_at
And then, the other tables would inherit from Publication, having the publication_id as PK,FK.
I leave here a diagram that shows how would it finally be if this approach is taken (I cannot embeded it but it but you can check it in the following link).
This way, I could retrieve all posts using a query on the Publication table. However, I have concerns about:
Query Performance: Would querying across multiple inherited tables be efficient in SQL Server?
Joins & Indexing: Since I'll frequently need to filter by type, will inheritance make indexing and joins more complex?
Alternative Approaches: Would it be better to store all post types in a single table with nullable columns, or should I just keep separate tables with no inheritance?
esta bien formulado?
