Dependency on 2 relationships to different tables

[Macgyver]

Rather than describe this problem in esoteric terms, I'll be specific. I'm helping a friend (it really is a friend) with a database for a roll-playing game (I'm guessing some of the people here know what those are).

For the simple example, I have the following tables:

- Character

- Skills (list of all available skills)

- Advantages (list of all available advantages)

- CharacterSkill Index (which characters have which skills and at what level)

- CharacterAdvantage Index (which characters have which advantages)

The trouble is with (what I'm assuming should be handled by) another table, which is:

- SkillAdvantageModifiers (lists skill/advantage combinations which result in a bonus to skill level)

So, if a character has the (for simplicity) 'Shoes' advantage, he gets a +3 to his 'Running' skill (if he has one).

The problem I'm trying to figure out is how to determine how to apply the bonus via a relationship (if that's even the best way), given the dependency on successful relationships between Character A having both Skill B and Advantage C. How/where do I set up relationships for SkillAdvantageModifiers? An added complication is that whatever mechanism is used needs to be able to accommodate multiple such modifiers for the same skill (two different advantages giving +1 each).

A sample file can be found here:

http://web.mac.com/oshea/Char.fp7

Edited June 23, 2006 by Guest

[Brian C]

I have toyed around with RPG tools for a long time as a hobby So here is a quick sample of how you could accomplish what you are trying to do:

Provided you want to actually keep a talbe of defined items, you could try something like this:

Relationships:

Character Inventory Items ItemMods

Character Inventory ItemMods (2 Inv)

Character Inventory InvMods Skills (2 InvMods)

Character ChrSkillIndex Skills

Character ChrSkillIndex InvMods (2 ChrSkill)

Add "Shoes of Running" to your character's inventory of items.

Your process should copy the info from ItemMods to InvMods. InvMods will now contain a Skill ID, a Skill Modifer, a Char ID, and an Item ID.

Now you have an inventory item that has a modifier connected to it and it is all tagged with your char id.

Your character has skills in the ChrSkillIndex. Your skills index is linked to your InvMods by the chrid and skill id.

You character skill calculation will have it’s home in the Chr Skill Index table.

It will then use the relationship to InvMods to change its result.

Clear as mud? :D

[Fitch]

Perhaps you could use a multi-line key in the CharacterSkill Index that would be populated with Advantages. E.g. the key field would contain something like:

Shoes

Socks

RollerSkates

...or better, the IDs of those items.

So the total skill would be Skill + Sum(related::advantages).

PS: I didn't find any sample file at that URL.

[Macgyver]

Okay, the URL to the sample file is fixed. Must have spaced out on that one.

[Brian C]

Our approachs are basically the same as to how you arrive at the result mathamatically, but using a multikey is not as flexible at least in the way Fitch wants to implement it in his initial example - though I am sure he can find a way to expand it to work as well as anything I have done

However in his current example, you would not have the ability to change attributes for items to make them specific to the character or the adventure and you would not have the ability to add notes or other links to the inventory item itself, such as location (worn or stored, if stored, where?) command words, history, etc.

I suppose in the end it just depends on how much work you want to put into this RPG tool and how flexible you want it to be.

If anything it is a fun thing to do to help you to learn FM better. :D

[Macgyver]

I really am just trying to help a friend out. His database is actually looking really complex at this point, which is why I created the sample DB to try to get this problem worked out.

I've looked at Brian's suggestion and am trying to keep it straight in my head (different gaming systems are causing slightly different visualizations between us, I think), but the crux appears to be the part where he says 'Your process should copy the info from ItemMods to InvMods'.

Are you proposing something accomplished via a lookup or script? I was trying to come up with a way to do this solely with relationships (such that it would not be breakable by a missed lookup or such). Or am I misunderstanding?

I don't suppose you'd be willing to illustrate on the sample? :)

[comment]

You need a calculation field in your CharacterSkillsIndex table, that contains a list of related AdvantageIDs from the CharacterAdvantagesIndex table. Then you can relate CharacterSkillsIndex to SkillAdvantageModifiers on TWO criteria:

CharacterSkillsIndex::SkillID = SkillAdvantageModifiers::SkillID

AND

CharacterSkillsIndex::cMyAdvantageIDs = SkillAdvantageModifiers::AdvantageID

The list can be gathered either by a value list, using related items only, or by a recursive custom function. Unfortunately, you will find that having one relationship depend on another causes screen refresh issues.

[Fitch]

If you are planning to scale this thing up to World of Warcraft size, you're going to have to make some compromises. Fully normalized schema are satisfying on a theoretical level, but in the real world their performance tends to fall flat. (I know I'm generalizing.) So at some point you'll want to consider using scripts and/or lookups to improve performance.

That said, I've looked at your graph, and it seems to me you should be able to relate Skills to SkillAdvantageModifiers and then create another CharacterAdvantages TO and relate SkillAdvantageModifiers to it and then to another Advantages TO.

I.e.

Char - CharSkill - Skill - SkillAdvMod - CharAdv2 - Adv2

Fitch - Fitch/Think - Think - Think/Coffee - Fitch/Coffee - Coffee(+2 Think)