I'm developing a simple worldbuilding app with Electron, and I want users to define their own custom data types, much like platforms such as Anytype or LegendKeep. For instance, a user could create a 'Character' object that has properties like name, description, and image. I'm currently using SQLite for my database, and I'm considering creating a new table for each distinct object. After some research, I've learned that using JSON could be a good fit, but I'm unsure if generating a new table for each type is really the way to go.
5 Answers
For local setups, you might want to stick with the local storage API or use something like electron-store. It's straightforward and can handle basic data storage without the overhead of a full database.
You might want to consider a NoSQL database for this kind of scenario. If you're sticking with SQL, you can serialize the custom properties as JSON and store them in a text column. If your RDBMS supports JSON, you could use a dedicated JSON column (SQLite uses text but has JSON functions available).
Dynamic table generation is generally not the best approach. It's a common practice to go for normalization—meaning you might want to use just one table for objects and another for properties. Using JSON could work for your needs, and remember, you can always migrate your structure later if required. Keep it simple at first!
Using PostgreSQL with a jsonb column is a solid option; you'll be able to query the data as needed. Create a table named 'data' with columns for id, type, and json_data (set as jsonb). This should give you a lot of flexibility in how you store and access your data.
Have you gotten started yet? Typically, you'd use a migration tool to keep your schema synced. If you're using Strapi, it offers REST endpoints, user authentication, and many handy features. If you choose PostgreSQL, look for its JSON data type, which allows you to store arbitrary data. For a simpler setup, consider a table for user attributes with keys and values per row.
Exactly! That way, you're flexible with the data structure without complicating your tables too much.