Data Modeling for Product Features

MediumSQL · PostgreSQL

Problem

How do you approach data modeling for new product features?

Use the provided feature, status history, and project relationship data to produce a concise feature-level view. Include features even when they have no status history or project assignments.

Output

One row per feature with feature_id, feature_name, current_status, project_count, and lifecycle_stage.
Order by feature_id ascending.

Output details

Use the status with the latest status_date, breaking ties by greatest status_history_id.
Set current_status to NULL when a feature has no status history.
Map NULL status to not_started; map proposed, beta, active, and deprecated to planned, validation, launched, and retired.
Map any other status to unknown.
Count project assignments, returning 0 when a feature has none.

Schema

product_features

Column	Type	Description
feature_idPK	INT	Unique product feature identifier
feature_name	VARCHAR(150)	Product feature name
owner_team	VARCHAR(100)	Team responsible for the feature
created_at	DATE	Date the feature was created

feature_status_history

Column	Type	Description
status_history_idPK	INT	Unique status history record identifier
feature_id	INT	Referenced product feature
status	VARCHAR(30)	Recorded feature status
status_date	DATE	Date the status became effective

feature_projects

Column	Type	Description
assignment_idPK	INT	Unique feature-project assignment
feature_id	INT	Referenced product feature
project_name	VARCHAR(150)	Project using or implementing the feature

Tablesproduct_featuresfeature_status_historyfeature_projects