Implement data models - Githubissues

To migrate and normalize your NoSQL schema into a relational database schema, we'll need to break down the nested fields and relationships into separate tables that follow relational principles like normalization and referential integrity.

Step 1: Analyze the NoSQL Schema

Here's a breakdown of your NoSQL schema:

Users: Each user can have multiple collections and assets, so this is a one-to-many relationship.
Assets: An asset has a creator_id (which references the user), an array of equipment (which may need to be normalized), and references to comments (which can be a separate table).
Comments: Each comment is tied to an asset and a user.
Collections: A collection can contain multiple assets.

Step 2: Define the Relational Schema

We’ll transform each of these entities into a relational table and manage their relationships using foreign keys. Here’s how we can design the relational schema:

1. Users Table

The Users table stores user information. We'll use the user’s unique _id as the primary key.

Column Name	Data Type	Description
user_id	INT or UUID	Primary Key, unique identifier for the user
display_name	VARCHAR	User's display name
email	VARCHAR	User's email (can be unique)
profile_picture	VARCHAR	URL to the profile picture
bio	TEXT	User's biography
joined_date	DATETIME	Date when the user joined

Primary Key: user_id

2. Assets Table

The Assets table stores asset information. The creator_id will be a foreign key that references the Users table.

Column Name	Data Type	Description
asset_id	INT or UUID	Primary Key, unique identifier for the asset
image_url	VARCHAR	URL of the image for the asset
type	VARCHAR	Type of the asset (e.g., image, video, etc.)
name	VARCHAR	Name of the asset (extracted from attributes)
race	VARCHAR	Race of the asset (extracted from attributes)
class	VARCHAR	Class of the asset (extracted from attributes)
backstory	TEXT	Backstory of the asset (extracted from attributes)
creator_id	INT	Foreign Key: references `Users(user_id)`
visibility	VARCHAR	Visibility setting for the asset
created_date	DATETIME	Date when the asset was created
likes	INT	Number of likes for the asset
shared_count	INT	Number of times the asset was shared

Primary Key: asset_id

Foreign Key: creator_id references Users(user_id)

3. Comments Table

The Comments table stores comment data. Each comment is associated with an asset and a user.

Column Name	Data Type	Description
comment_id	INT or UUID	Primary Key, unique identifier for the comment
asset_id	INT	Foreign Key: references `Assets(asset_id)`
user_id	INT	Foreign Key: references `Users(user_id)`
content	TEXT	The content of the comment
created_date	DATETIME	Date when the comment was created
last_updated	DATETIME	Date when the comment was last updated

Primary Key: comment_id

Foreign Keys: asset_id references Assets(asset_id), user_id references Users(user_id)

4. Collections Table

The Collections table stores collection information. The user_id will be a foreign key, and each collection will reference multiple assets, requiring a many-to-many relationship between assets and collections.

Column Name	Data Type	Description
collection_id	INT or UUID	Primary Key, unique identifier for the collection
name	VARCHAR	Name of the collection
description	TEXT	Description of the collection
visibility	VARCHAR	Visibility setting for the collection
user_id	INT	Foreign Key: references `Users(user_id)`

Primary Key: collection_id

Foreign Key: user_id references Users(user_id)

5. Assets_Collections (Many-to-Many Relationship)

Since each collection can have many assets, and each asset can belong to many collections, we need a join table to represent this many-to-many relationship.

Column Name	Data Type	Description
collection_id	INT	Foreign Key: references `Collections(collection_id)`
asset_id	INT	Foreign Key: references `Assets(asset_id)`

Primary Key: (collection_id, asset_id) - Composite key for this table

Foreign Keys: collection_id references Collections(collection_id), asset_id references Assets(asset_id)

Step 3: Example Data Models

Users Table Example:	user_id	display_name	email	profile_picture	bio	joined_date
1	Jane Doe	jane@example.com	/images/jane.jpg	"Bio of Jane"	2024-01-01

Assets Table Example:	asset_id	image_url	type	name	race	class	backstory	creator_id	visibility	created_date	likes	shared_count
1001	/assets/asset1.jpg	image	Sword	Elf	Mage	"A magic sword"	1	public	2024-11-01	50	200

Comments Table Example:	comment_id	asset_id	user_id	content	created_date	last_updated
2001	1001	1	"Nice asset!"	2024-11-02	2024-11-02

Collections Table Example:	collection_id	name	description	visibility	user_id
5001	My Collection	"A collection of assets"	public	1

Assets_Collections (Join Table) Example:	collection_id	asset_id
5001	1001

Step 4: Foreign Keys & Indexing

Foreign Keys:
- creator_id in the Assets table references user_id in the Users table.
- asset_id in the Comments table references asset_id in the Assets table.
- user_id in the Comments table references user_id in the Users table.
- user_id in the Collections table references user_id in the Users table.
- asset_id and collection_id in the Assets_Collections table reference Assets and Collections.
Indexes: Consider adding indexes for:
- email in the Users table (to make lookups efficient).
- created_date in Assets, Comments, and Collections to speed up date-based queries.
- Foreign key fields like creator_id in the Assets table for efficient lookups.

Conclusion

By following this relational schema:

We’ve normalized the data to reduce redundancy (e.g., separating assets and collections into different tables).
We’ve used foreign keys to establish relationships between tables (e.g., linking users to assets and collections).
The join table (Assets_Collections) allows us to represent the many-to-many relationship between assets and collections.

This structure ensures data integrity, supports efficient queries, and prepares your application for growth as data becomes more complex.

tabletop-generator / server

Implement data models #42