Newbie Normalizing Question 2

[MarzGuitarz]

Hello I am designing my first access database to store information on a role-playing game I have created. My problem is that I can't figure out how to normalize my weapons charts which are currently on excel.

Each weapon has a name, cost, size, type; damage which is is standard stuff. The problem is that each weapon has a table of eight different ranks as the player improves from Novice to Grand Master displaying numbers for; Offensive#, Fumble, Lethal and points needed to obtain the rank .

an example:

Rank >> No Skill Novice Apprentice
Offensive 9 13 17
Points needed 0 30 50
Fumble 88 90 92
lethal 6 6 7

As you can see by the time I get through 8 ranks all the way to Grand master there is a lot of data for each weapon. I have around 30 diffeent weapons.

Do I make one huge table? Do I create a seperate table for each rank or somthing else.
I would greatly appreciate any help possible.
Thank you

 

[willir]

MarzGuitarz

Two tables seems to make the most sense for weapons, but you may need some more...

tblWeapon
WeaponID - primary key
WeaponName
WeaponType - text (melee, range)

tblRank
Rank - primary key

tblWeaponDamage
WeaponDamageID - primary key
WeaponID - foreign key to tblWeapon
OffensePoints
PointsNeeded
Fumble
Lethal

I create tblRank here because you will probably end up using Rank in varous other places too.

Now let's look at something else.

Say you have a "tblClass" where only certain classes of heros / villans can handle certain weapons.

tblClass
Class - prmiary key
ClassName

Now tie this in with weapons. Certain classes of heros can handle many weapons. A weapon can be handled by different classes. This is a many-to-many relationship, so you need to create a joiner table.

tblWeaponClass
Class - foreign key to tblClass
WeaponID - foriegn key to tblWeapon

Richard

 

[MarzGuitarz]

Wow that gave me a lot to think about. Thanks very much. That was a grat help.

 

[LittleSmudge]

So you have an inherent understanding of FRPG as well Richard. Are there no limits to your diversity ?

However, my interpretation of what MarzGuitarz is asking leads me to :-


tblWeapon
WeaponID - primary key
WeaponName
WeaponType - text (melee, range)
other
weapon
specific
fields


tblRank
Rank - primary key


tblWeaponRank
WeaponRef - foreign key to tblWeapon
RankRef - foreignKey to tblRank
OffensePoints
PointsNeeded
Fumble
Lethal

Make tblWeaponRank a Linking table in a many to many between tblWeapon & tblRank
Make the two FK keys WeaponRef and RankRef into a Join PrimeKey for the table ( Std practice for Many-Many linking tables where only one link should exist )

Thus Every Weapon can link to every Rank ( and when the 9th Rank ( Grand Supreme Master ) comes along it is a simple case of adding more data to tblWeaponRank.

And in each case of a link between Weapon and Rank a complete set of OffensePoints, PointsNeeded, Fumble, Lethal statistics can be stored.



Comments ?




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[willir]

You are right of course Graham.

I missed the inclusion of Rank in...

tblWeaponDamage
WeaponDamageID - primary key
WeaponID - foreign key to tblWeapon
Rank - foreign key to tblRank
OffensePoints
PointsNeeded
Fumble
Lethal

My kids are into RPG, and as a single parent, I picked up a bit by osmosis. However, in addition to rank, can other parameters affect the statistics of a weapon??

For example, in addition to experience (rank), could class, race (as in species), etc influence the statistics?

I realize that a character will have their own statistics such as strength, agility, etc, which affects damage, but one could also consider other factors.

For example, a thief with a knife may inflict more damage than an elf with a knife even though the thief and elf have the same character stats. A character with a "good" or "noble" character would be less willing to use a knife and more likely to use a sword whereas an "evil" character would be more willing to use a knife.

With this in mind, the weapon table would store the primary statistics and other tables could store the influencing statistics. For example, OffencePoints = 10. A thief with a knife inflicts +2 more OffencePoints for a total of 12 points where an elf would inflict +0 for a total of 10.

This certainly would be more complicated.

tblWeapon
WeaponID - primary key
WeaponName
WeaponType - text (melee, range)
OffensePoints
PointsNeeded

tblWeaponDamage
WeaponDamageID - primary key
WeaponID - foreign key to tblWeapon
Rank - foreign key to tblRank
OffenseModifier
PointsNeededModifier
Fumble
Lethal

tblWeaponClass
Class - foreign key to tblClass
WeaponID - foriegn key to tblWeapon
OffenseModifier
PointsNeededModifier

tblWeaponRace
WeaponID - foriegn key to tblWeapon
RaceID - foriegn key to tblRace
OffenseModifier
PointsNeededModifier

Just rambling I guess.

 

[lespaul]

and let's not forget there are some weapons that cannot be used by certain races/character types. Does that apply too?

Leslie

 

[willir]

Hi Leslie ;-)

With your passion for Jordan, I thought you might chime in too.

Say you have a "tblClass" where only certain classes of heros / villans can handle certain weapons.

Say, let's get together and create our own gaming tool. The AccessStats Calcualtor for RPG-Serious

...perhaps we could go visit Graham in Europe. I personally am getting a little tired of the snow. (Yea, I know, move to NM)

 

[lespaul]

you wish, we had 8 - 12 inches in the city Tuesday!! hasn't been that much snow here (in the city) in 20 years!!! The areas a little north of here that actually have gotten some snow in recent years got HAMMERED with 18 - 24 inches!! Of course it all melted in the almost 60 degree weather the following two days!! All the ski resorts are extending a few extra weeks to take advantage of the "Snow God Blessing"!

Of course, I'll head to Europe with you anytime - we'll have to be sure to bring books for each other that we haven't read yet!!!

Leslie

 

[willir]

Thanks Leslie for my moment chuckles.

Yea, I have never been to NM so I had to show my ignorance again. I realize that the altitude is higher, hence the snow. My wife from Kansas would really start to get cabin fever in February because her Mom would call to tell her about the "glads" blooming in Kansas and then she looked out the window at the snow and six or more 8 weeks of waiting. I thought you being south, would have started spring by now.

The thing about winter is that you have to enjoy it ... or grab a D&D mannual and call your fellow gamers (that's my kids speaking of course).

Hope your Playtapus project is well in hand. Its been a while since you solicited advice.

 

[lespaul]

oh we did start spring!! The Saturday & Sunday before the storm we were in the 70's!!! My roses were growing and my hollyhocks were getting BIG!!! now they are all dying

The Platypus is devolving!! We are about 1/3 of the way through the new design. It's really hard to design something when you get conflicting information about what's happening!!



Leslie