Database schema

[curtisdelicata]

Database Schema for Persistent Browser-Based Game

Issue Description

Overview

We need to design and implement a comprehensive database schema for our persistent browser-based game. The game involves player management, in-game resources, quests, items, inventory management, and guilds. The schema should be scalable and efficient, supporting the game's functionality and ensuring data integrity.

Requirements

The database schema should include, but not be limited to, the following tables and their relationships:

1. Players

   • id (primary key)
   • username (unique)
   • email (unique)
   • password
   • level
   • experience
   • created_at
   • updated_at

2. Player_Profiles

   • id (primary key)
   • player_id (foreign key)
   • avatar_url
   • bio
   • created_at
   • updated_at

3. Guilds

   • id (primary key)
   • name (unique)
   • description
   • created_at
   • updated_at

4. Guild_Memberships

   • id (primary key)
   • player_id (foreign key)
   • guild_id (foreign key)
   • role (e.g., leader, member)
   • joined_at
   • created_at
   • updated_at

5. Items

   • id (primary key)
   • name
   • description
   • type (e.g., weapon, armor, potion)
   • rarity
   • created_at
   • updated_at

6. Player_Items

   • id (primary key)
   • player_id (foreign key)
   • item_id (foreign key)
   • quantity
   • created_at
   • updated_at

7. Quests

   • id (primary key)
   • name
   • description
   • experience_reward
   • item_reward_id (foreign key, nullable)
   • created_at
   • updated_at

8. Player_Quests

   • id (primary key)
   • player_id (foreign key)
   • quest_id (foreign key)
   • status (e.g., in-progress, completed)
   • created_at
   • updated_at

9. Resources

   • id (primary key)
   • player_id (foreign key)
   • resource_type (e.g., gold, wood, stone)
   • quantity
   • created_at
   • updated_at

Additional Notes

• All foreign keys should have proper indexing and cascading rules to maintain referential integrity.
• Use appropriate data types for each field, ensuring efficiency and scalability.
• Consider implementing soft deletes where applicable.
• Utilize Laravel’s built-in migrations, seeders, and factories for creating and populating the database tables.
• Ensure the schema adheres to best practices for database normalization and performance optimization.
• Implement relationships using Laravel's Eloquent ORM conventions for easier query building and data manipulation.

Acceptance Criteria

• The database schema is fully defined with all necessary tables, fields, and relationships.
• Migrations are created and can be successfully run to set up the database schema.
• Basic seed data is available to test the initial setup.
• Documentation is provided explaining the schema and any important design decisions.

References

• Laravel Migrations Documentation
• Database Design Best Practices
• Laravel Eloquent Relationships

Tasks

1. Define and document the database schema.
2. Create migrations for each table.
3. Implement seeders for initial data.
4. Test the migrations and seeders to ensure correctness.
5. Provide comprehensive documentation for the schema.

Assignees

• @your-username

Labels

• enhancement
• database
• laravel
• game-development

Milestone

• Version 1.0

Please let me know if there are any additional requirements or changes needed.

[nicotravassos]

Finally someone is building a pbbg game using laravel and livewire keep up the good work @curtisdelicata

Some cool things to help you for your journey and database structure: https://github.com/AdamKyle/flare/tree/master/app/Flare/Models

[curtisdelicata]

Thank you @nicotravassos