Hey all. I'm in the process of building an EER model of a merit system for my custom player management system which will be a php/mysql website when its done. The problem I have been having is that this is a very complex system. It started off being very simple but when I identified all the features I wanted to include into the website it turns out to be alot and complex. This is why its very important that I acurrately describe EER model so it can be used to build a database to supoort my website in the end. My background in EER modeling is very limited but I do understand that it needs to be done if I am going to have any chance of building a database capible of supporting my users.

I need assistance from anyone who can help me finish this EER model of the merit system accurately. Here is what I have come up with so far.
wiki ffxi merit reference: http://wiki.ffxiclopedia.org/wiki/Merits


Entities:
Members, Chars, Jobs classes, merits

Constraints:
A member can have 1 or more characters.
A character has 1 or more job classes.
A character has 'General Merits' which is used and affected by all jobs classes.
A job has 'Job Specific Merits' which only affects the particular job classes.

Each Merit type 'General Merits' and 'Job Specific Merits' have 3 basic constraints and rules:
Max combo (total number of upgrades per merit category).
Max per item (max number of upgrades per item).
Cost per upgrade (each item has an upgrade cost that can increase. the more you upgrade a merit item the points it will cost for the next upgrade e.g 1st upgrade may cost 1 merit points, 2nd upgrade of same item may cost you 3 merit points.)

General Merits have 5 main types merits

HP / MP - Max combo: 8, Max per item: 8, Cost per upgrade [1>2>3>4>5>5>5>5]
>HP (+10 per upgrade)
>MP (+10 per upgrade)

Attributes - Max combo: 5, Max per item: 5, Cost per upgrade [3>6>9>9>9]
>STR (+1 per upgrade)
>DEX (+1 per upgrade)
>VIT (+1 per upgrade)
>AGI (+1 per upgrade)
>INT (+1 per upgrade)
>MND (+1 per upgrade)
>CHR (+1 per upgrade)

Combat Skills - Max combo: 20, Max per item: Varies depending on item, Cost per upgrade [1>2>3>3>3>3>3>3]
/* here is when it gets tricky. the Combat skills are divided into 2 sub categories (Weapon Skills, Defensive Skill) which inherit its parent constraints of Max combo and Cost per upgrade. */

Combat Skills>Weapon Skills - Max per item: 8, Cost per upgrade [1>2>3>3>3>3>3>3]
>Hand-to-Hand Skill (+2 per upgrade)
>Dagger Skill (+2 per upgrade)
>Sword Skill (+2 per upgrade)
>Great Sword Skill (+2 per upgrade)
>Axe Skill (+2 per upgrade)
>Great Axe Skill (+2 per upgrade)
>Scythe Skill (+2 per upgrade)
>Polearm Skill (+2 per upgrade)
>Katana Skill (+2 per upgrade)
>Great Katana Skill (+2 per upgrade)
>Club Skill (+2 per upgrade)
>Staff Skill (+2 per upgrade)
>Archery Skill (+2 per upgrade)
>Marksmanship Skill (+2 per upgrade)
>Throwing Skill (+2 per upgrade)

Combat Skills>Defensive Skills - Max per item: 4, Cost per upgrade [1>2>3>3]
>Guarding Skill (+2 per upgrade)
>Evasion Skill (+2 per upgrade)
>Shield Skill (+2 per upgrade)
>Parrying Skill (+2 per upgrade)
/* end of sub categories for Combat Skills */


Magic Skills - Max combo: 16, Max per item: 8, Cost per upgrade [1>2>3>3>3>3>3>3]
>Divine Magic Skill (+2 per upgrade)
>Healing Magic Skill (+2 per upgrade)
>Enhancing Magic Skill (+2 per upgrade)
>Enfeebling Magic Skill (+2 per upgrade)
>Elemental Magic Skill (+2 per upgrade)
>Dark Magic Skill (+2 per upgrade)
>Summoning Magic Skill (+2 per upgrade)
>Ninjutsu Skill (+2 per upgrade)
>Singing Skill (+2 per upgrade)
>String Instrument Skill (+2 per upgrade)
>Wind Instrument Skill (+2 per upgrade)
>Blue Magic Skill (+2 per upgrade)


Other - Max combo: 8, Max per item: 4, Cost per upgrade [1>2>3>4]
>Enmity Increase (+1 per upgrade)
>Enmity Decrease (-1 per upgrade)
>Critical Hit Rate (+1% per upgrade)
>Enemy Critical Hit Rate (-1% per upgrade)
>Spell Interruption Rate (-2% per upgrade)

/* I wont get into the Job specific types for now since I think this is already complicated enough. I have also left out the job entity in the EER*/

I am using the chen notation for EER modeling since I only have acess to that. I hope theres someone out there you can help me.

This post has been edited by sonesay: Nov 18 2007, 02:12 AM

got an update. I think I'm getting there with the EER model, still not 100% sure if its correct enough to implement but I will make a test database and see if it handles all my required transaction queries once I finish the EER model.

I'm guessing not many people use ER or EER models for database development since there isnt many response. There are alot of sites out there and books on this topic but like most things you need to practice yourself to understand it better hence why I'm doing this as a project.