Physical Database Design
DB名 : minder_mutunet_db
1. mutunet_users
| Column Name |
Data Type |
Constraints |
Index |
Description |
id |
BIGINT |
PRIMARY KEY, AUTO_INCREMENT |
PK |
User ID |
email |
VARCHAR(255) |
NOT NULL |
UNIQUE |
Email Address |
password_hash |
CHAR(64) |
NOT NULL, CHARACTER SET ascii |
– |
Password Hash |
is_deleted |
BOOLEAN |
NOT NULL, DEFAULT FALSE |
– |
Deletion Flag |
created_at |
DATETIME |
NOT NULL, DEFAULT CURRENT_TIMESTAMP |
– |
Creation Timestamp |
updated_at |
DATETIME |
NOT NULL, DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP |
– |
Update Timestamp |
2. user_attributes
| Column Name |
Data Type |
Constraints |
Index |
Description |
id |
BIGINT |
PRIMARY KEY, AUTO_INCREMENT |
PK |
Attribute ID |
user_id |
BIGINT |
NOT NULL, FOREIGN KEY (users.id) |
INDEX, UNIQUE(user_id, attribute_key) |
User ID |
attribute_key |
VARCHAR(255) |
NOT NULL |
UNIQUE(user_id, attribute_key) |
Attribute Key |
attribute_value |
TEXT |
NULL |
– |
Attribute Value |
created_at |
DATETIME |
NOT NULL, DEFAULT CURRENT_TIMESTAMP |
– |
Creation Timestamp |
updated_at |
DATETIME |
NOT NULL, DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP |
– |
Update Timestamp |
3. tracks
| Column Name |
Data Type |
Constraints |
Index |
Description |
id |
BIGINT |
PRIMARY KEY, AUTO_INCREMENT |
PK |
Track ID |
user_id |
BIGINT |
NOT NULL, FOREIGN KEY (users.id) |
INDEX |
User ID |
instrument_name |
VARCHAR(100) |
NOT NULL |
INDEX |
Instrument Name |
file_url |
TEXT |
NOT NULL |
– |
File URL |
is_deleted |
BOOLEAN |
NOT NULL, DEFAULT FALSE |
– |
Deletion Flag |
created_at |
DATETIME |
NOT NULL, DEFAULT CURRENT_TIMESTAMP |
– |
Creation Timestamp |
updated_at |
DATETIME |
NOT NULL, DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP |
– |
Update Timestamp |
4. track_attributes
| Column Name |
Data Type |
Constraints |
Index |
Description |
id |
BIGINT |
PRIMARY KEY, AUTO_INCREMENT |
PK |
Attribute ID |
track_id |
BIGINT |
NOT NULL, FOREIGN KEY (tracks.id) |
INDEX, UNIQUE(track_id, attribute_key) |
Track ID |
attribute_key |
VARCHAR(255) |
NOT NULL |
UNIQUE(track_id, attribute_key) |
Attribute Key |
attribute_value |
TEXT |
NULL |
– |
Attribute Value |
created_at |
DATETIME |
NOT NULL, DEFAULT CURRENT_TIMESTAMP |
– |
Creation Timestamp |
updated_at |
DATETIME |
NOT NULL, DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP |
– |
Update Timestamp |
5. projects
| Column Name |
Data Type |
Constraints |
Index |
Description |
id |
BIGINT |
PRIMARY KEY, AUTO_INCREMENT |
PK |
Project ID |
user_id |
BIGINT |
NOT NULL, FOREIGN KEY (users.id) |
INDEX |
User ID |
| status_id |
TINYINT |
NOT NULL, DEFAULT 1, FOREIGN KEY (mst_project_statuses.id) |
FK |
Project Status ID (Default 1: Calling for collaborators) |
is_deleted |
BOOLEAN |
NOT NULL, DEFAULT FALSE |
– |
Deletion Flag |
created_at |
DATETIME |
NOT NULL, DEFAULT CURRENT_TIMESTAMP |
– |
Creation Timestamp |
updated_at |
DATETIME |
NOT NULL, DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP |
– |
Update Timestamp |
6. project_attributes(Project Attributes)
An EAV structure table for managing variable attributes such as project title, summary, genre and the URL for the final audio file.
| Column name |
Data types |
Constraints |
Index |
Explanation |
| id |
BIGINT UNSIGNED |
PRIMARY KEY, AUTO_INCREMENT |
PRIMARY |
Property ID |
| project_id |
BIGINT UNSIGNED |
NOT NULL, FOREIGN KEY (projects.id) ON DELETE CASCADE |
FK |
Target Project ID |
| attribute_key |
VARCHAR(255) |
NOT NULL |
INDEX |
Properties name（title, description, genre, completed_track_url ） |
| attribute_value |
TEXT |
NULL |
– |
Property value |
| created_at |
TIMESTAMP |
NULL |
– |
Date created |
| updated_at |
TIMESTAMP |
NULL |
– |
Date and time of update |
7. mst_project_track_statuses
| Column Name |
Data Type |
Constraints |
Index |
Description |
id |
INT |
PRIMARY KEY, AUTO_INCREMENT |
PK |
Status ID |
name |
VARCHAR(50) |
NOT NULL |
UNIQUE |
Status Name |
created_at |
DATETIME |
NOT NULL, DEFAULT CURRENT_TIMESTAMP |
– |
Creation Timestamp |
8. project_tracks
| Column Name |
Data Type |
Constraints |
Index |
Description |
id |
BIGINT |
PRIMARY KEY, AUTO_INCREMENT |
PK |
Project Track ID |
project_id |
BIGINT |
NOT NULL, FOREIGN KEY (projects.id) |
UNIQUE(project_id, track_id) |
Project ID |
track_id |
BIGINT |
NOT NULL, FOREIGN KEY (tracks.id) |
INDEX, UNIQUE(project_id, track_id) |
Track ID |
status_id |
INT |
NOT NULL, FOREIGN KEY (mst_project_track_statuses.id) |
INDEX |
Status ID |
created_at |
DATETIME |
NOT NULL, DEFAULT CURRENT_TIMESTAMP |
– |
Creation Timestamp |
9. favorite_users
| Column Name |
Data Type |
Constraints |
Index |
Description |
id |
BIGINT |
PRIMARY KEY, AUTO_INCREMENT |
PK |
Favorite User Entry ID |
user_id |
BIGINT |
NOT NULL, FOREIGN KEY (users.id) |
UNIQUE(user_id, favorite_user_id) |
User ID |
favorite_user_id |
BIGINT |
NOT NULL, FOREIGN KEY (users.id) |
INDEX, UNIQUE(user_id, favorite_user_id) |
Favorite User ID |
display_order |
INT |
NOT NULL, DEFAULT 0 |
– |
Display Order |
created_at |
DATETIME |
NOT NULL, DEFAULT CURRENT_TIMESTAMP |
– |
Creation Timestamp |
10. favorite_projects
| Column Name |
Data Type |
Constraints |
Index |
Description |
id |
BIGINT |
PRIMARY KEY, AUTO_INCREMENT |
PK |
Favorite Project Entry ID |
user_id |
BIGINT |
NOT NULL, FOREIGN KEY (users.id) |
UNIQUE(user_id, project_id) |
User ID |
project_id |
BIGINT |
NOT NULL, FOREIGN KEY (projects.id) |
INDEX, UNIQUE(user_id, project_id) |
Favorite Project ID |
display_order |
INT |
NOT NULL, DEFAULT 0 |
– |
Display Order |
created_at |
DATETIME |
NOT NULL, DEFAULT CURRENT_TIMESTAMP |
– |
Creation Timestamp |