Database Design: Multiple databases
Hello Appwrite Community,
I have a question regarding efficient database design with Appwrite. I've been trying to gather relevant information and have created a database structure that works well, but I have questions about scalability, clarity, and maintenance.
App Structure: Players can form gaming groups worldwide. Players share information like locations, scenarios, costumes, etc.
Current Database Design: Collections: Games, Locations, Scenarios, Costumes, Maps. Each object has a "gameID" attribute to link it to a specific game.
Challenge: Each game can have many maps, and each map can hold hundreds of small objects that describe canvas-placed objects (e.g., {canvasID="canvasUUID", type="player", pos.dx=2342.4, pos.dy=645.4, name="Player1", color="ffffff", rotation=".54", children="{uuid1, uuid2}, connection=null}). Each game can have many scenarios, and each scenario consists of dozens of longer text elements. Storing all these objects and text elements in a single collection for all games seems insame to me, especially since the map elements need to be kept separate (not one bigger JSON for all elements on one canvas) for real-time updates of individual elements in the canvas editor.
My New Solution: Create a new database for each new game. Collections and schemas within these databases are identical.
I read a statement in Discord that made me uncertain about this approach: "But generally, if your DB design ends up by creating new databases or collections at runtime, you're probably doing something wrong or looking in the wrong direction."
Questions: Is this approach efficient? Will tens of thousands of small databases cause problems? Is there a better way to handle this?
I would appreciate your thoughts and assistance.
Thank you!
I recommend not creating databases dynamically and having a fixed number of them. Having a new DB per user doesn't seems to be very efficient
Because that implies creating a tables for each new collection inside the DB
Thank you for your Response! I apologize for asking again, but this question seems critically important to me. To clarify my issue with the high numbers, I have sketched both structures again.
How does one typically handle such large numbers of Objects? Should I, in the case of a single database, perhaps move the canvas objects that are no longer needed daily into an archive collection? I noticed that databases and collections can be disabled. Would a disabled collection serve as a good archive?
Multiple Databases:
Appwrite Project
│
└── Databases (for each game => 10K+ Databases)
├── Collections:
│ ├── Locations (about 20-50 Objects)
│ │ ├─ LocationName
│ │ ├─ LocationAddress
│ │ └─ ..
│ │
│ ├── Team (1)
│ │ ├── UserIDs[]
│ │ └── ..
│ │
│ ├── Scenarios (about 20-300 Objects)
│ │ ├─ ScenarioName
│ │ ├─ ScenarioDescriptions
│ │ └─ ..
│ │
│ ├── Maps/Canvas (about 20-300 Objects)
│ │ ├─ CanvasID
│ │ ├─ CanvasName
│ │ └─ ..
│ │
│ ├── CanvasObjects (about 200-10000 Objects)
│ │ └── CanvasObjects
│ │ ├─ CanvasID
│ │ ├─ Type
│ │ ├─ Pos.dx
│ │ ├─ Pos.dy
│ │ ├─ Name
│ │ ├─ Color
│ │ ├─ Rotation
│ │ ├─ Children[]
│ │ └─ Connections[]
│ │
│ └─ more Collections ...
│
└─ more Databases …
Single Database
Appwrite Project
│
└── Database (1)
├── Collections:
├── Games (10K+)
│ ├─ GameID
│ ├─ GameName
│ ├─ UserIDs[]
│ └─ ..
│
├── Locations (10K+ * about 20-50 Objects)
│ ├─ LocationName
│ ├─ LocationAddress
│ └─ ..
│
├── Scenarios (10K+ * about 20-300 Objects)
│ ├─ ScenarioName
│ ├─ ScenarioDescriptions
│ └── ..
│
├── Maps/Canvas (10K+ * about 20-300 Objects)
│ ├─ CanvasID
│ ├─ CanvasName
│ └─ ..
│
├── CanvasObjects (10K+ * about 200-10000 Objects) (= 100.000.000 ?!)
│ └── CanvasObjects
│ ├─ CanvasID
│ ├─ Type
│ ├─ Pos.dx
│ ├─ Pos.dy
│ ├─ Name
│ ├─ Color
│ ├─ Rotation
│ ├─ Children[]
│ └─ Connections[]
│
└── more Collections ..
