Hey guys, first time poster. Game design question:

I have been working on a top down 2d rpg for a while now. I have integrated Lua/Tolua++ as well as sqlite3 support. I have been able to use both to create pretty much anything I need in the game.

Problem: I continually run into architecutural design issues when working with this setup. I have gotten to the point where I could practically use the database for everything in the game or I could create everything in Lua. For example, I could write out a script to spawn all the NPCs in the game, assign sprites, names, dialogue, etc. however, I could do the exact same thing with some interesting database design and/or queries. As you can tell, there is a gray area I need to flesh out before moving forward to prevent future architecture changes. Currently, my Lua script calls something like, SpawnNPC(12) and my c++ core runs a query with a bunch of joins and gets all the information it needs to create the object. If I go even further, I could write a Lua function like, SpawnAllNPCs(map) that would make my engine ultimately create all the objects for the give map. My issue is that I don't know how much flexibility I should leave to Lua and how much control SQLite should have.

My question: what part of the game should be controlled by Lua/Tolua++ (simply because lua is a far better choice for controlling that part of the game) and what part should be done via queries (again, because SQLite3 would be a better choice for controlling that game).

Specifically: creating entities, controlling entity actions, using spells/abilities/menus, etc.

Any help is appreciated!

Sqlite is for long term storage, if you need it later on then just querry it. Keep in mind that querry processes can be slow, minimize the number you do per game loop.

Let me clarify:

As of right now, I only make SQL calls on entity creation. The object creation call is made form Lua, then C++ does a query on what info it needs, creates the object and pushes it to a vector and closes the SQL connection. Anything after that point is done by accessing the vector.

I assume by what you are saying that things like entity creation should be handled with a simplified Lua call and Query but things like existing entity movement triggers should be handled by Lua because it is faster and reduces the total number of queries needed. Also, because what I am storing in the first database is basically sprite file paths, character stats, ability stats, etc. These are only needed on creation.

For example, two scenarios:
Creating an NPC-> initiate from Lua, Query DB from C++, create object and push to vector.
Move an NPC->initiate from Lua, get object by name in vector, move. No query needed.

Does this design make sense? Similar design across the board with menus, spells, player characters, etc?

That works, just minimize the number of querries at all costs.