table design question

[DM_Vecna]

Well I finally got NWNX up and running. Thanks to those that helped!!!

Knowing a fair bit about scripting I am really pleased to be incorporating some wonderful systems like persistent journals into my PW.

But now I have moved on to switch over the Personal Reputation system found on the vault from the Bioware database to NWNX. By copying a lot of what the journal system does I dont think this is going to be that hard

But in creating a new table for this I ran into a question.
My table will have the following columns.
-ID
-Player account
-player Character name
-NPC
-a historic value determined by past interaction. (will be adjusted up and down)

Now am I better leaving these as they are and adding a new line for each new NPC that the PCs might interact with? This could easily become 70 NPCs for each PC.

Or am I better making a column for each NPC so that each PC will only have one line with 70 columns for the various NPCs each containing their historic value.

Thanks so much!

[Fireboar]

The first option is better. You really don't want to be adding a new coulmn for every NPC you create, and most of these values will be zero. I suggest, however, you make use of a link table.

player:
- id
- account
- character name

reputation:
- player_id
- npc
- reaction

Have reputation.player_id and reputation.npc a composite primary key for reputation, and player.id a primary key for player. player.id should be in a one-to-many relationship with reputation.player_id.

Then you can store the player id on the player as a local variable, and use a query on the reputation table. You could also use joins to eliminate the need to store player ID, but I find a lot of things work well if players each have their own row in a "players" table, and other systems link in from there using a unique ID for each person, therefore storing player ID as a local variable is the cleanest and most efficient way.