Verrrry basic databse questions

[BelowTheBelt]

So I made the decision to migrate from NBDE to SQLite in the hopes of increased efficiency and potentially more dynamic uses of my data.

However, I am not a database guy and have only the NWN database as a reference point that learned from. As a result, I'm a bit disoriented - I know even SQLite is more dynamic than NBDE, but am having a hard time conceptualizing the benefits. I think NBDE warped my thinking about how to approach databases and need help breaking out of the mental box that it put me in.

I have installed NWNX2 and the demo mod and successfully created/stored/retrieved the string and the object.

With my mod using NBDE, I have 26 campaign "tables" that different data are stored to. A sample of the tables include:
faction data
Player region
dmfi
persistent clock
guild
journals
message board
quest boss settings
...and the list goes on.

My questions may be very basic, but would be extremely helpful to hear from some experts who have some experience with these basic types of data storage/retrieval efforts and also more advanced usage.

My specific questions:
1) Should I seek to replicate each individual table in SQLite to replace the NBDE writes (vs. the standard pwdata table that the aps_demo mod creates)?
2) NBDE needed "flushing" to write the data to the db. Does SQLite require the same thing occasionally?
3) How could the idea of a relational database provide benefits over NBDE given the above tables (i.e. what do I need to consider to evaluate the need to link tables or given the example tables above, what kind of benefits could linking the tables provide? What are some other possible benefits?)
4) If I were to create a new table, do i need to name the fields as the name they'll be called in the script or can I set it to a generic name?
For example, in my database wrapper script, a call to SetDBInt rearranges the data to write to the aps_include function of SetPersistentInt

void SetDBInt(string sCampaign, string sVariable, int nValue, object oPC)
{
//NBDE_SetCampaignInt(sCampaign,sVariable,nValue,oPC);
SetPersistentInt (oPC, sVariable, nValue, 0, sCampaign);
}

Then, if a particular table would have the following:
SetDBInt ("Message Board", "NumPosts", 10, oPC), does my SQLite table need to have a field called "NumPosts" or would a field name "sVariable" be sufficient?

Thank you for your help. Your insight is much appreciated.
_________________
www.arenthyor.com

[Zebranky]

I've never used NBDE (nor the campaign DB in general), but I'll give my two cents.

[BelowTheBelt]

Thanks, that clears it up a lot.

Regarding the example of SetPersistentInt....

It appears that each of the aps_demo functions involve writing to an object (oPC in the example). What if I just want to write some data to a table for retrieval later?

For example, I"ve set up the following table and fields:

SendMessageToPC(GetLastUsedBy(), "Creating Table 'AR_BOSS_DB' for SQLite...");
SQLExecDirect("CREATE TABLE AR_BOSS_DB (" +
"dungeon varchar(3) NOT NULL default '000'," +
"quest_setting INTEGER (2) NOT NULL default '00'," +
"expire int(11) default NULL," +
"last timestamp NOT NULL default current_timestamp," +
"PRIMARY KEY (dungeon)" +
")");

so...in order to write to this database, I can't use the SetPersistentInt because there's no object as required by the function, correct? Does that mean that rather than using a function I have to use some SQL command like INSERT to write to the db?

Thanks.
_________________
www.arenthyor.com

[Zebranky]

[BelowTheBelt]

So SetPersistentInt writes to both the object (oPC or oModule or another object) AND to a table in the database? Any sense of why that was done (maybe to have the data preloaded in mod when it's set)? Seems a bit redundant to write it to both spots.

Just trying to wrap my head around when would be appropriate to use the SQLite db or when the campaign db or writing to an object in the game would be about the same.

I'm thinking the best candidates are sets of large records that get accessed frequently (spawn lists, persistent item storage, merchant stores, complicated crafting systems, player factions... )

Are there benefits to doing smaller tables that won't be accessed much? In the previous example, there will probably be less than 100 records that will only infrequently be accessed (client login, each quest completion, occasional dm tool use...).

Thanks for answering my questions.
_________________
www.arenthyor.com

[Zebranky]