I am doing a simple web app for my friend, and I am going round in circles trying to work out the best way to map some of this stuff. First of all I am using MVC and have separate models for my domain/data/ui logic. The UI heavily uses AJAX so although there is an *ideal* hierarchy for things, everything needs to be able to update itself.

My first issue was that I had a "character" table, that had an id, created date and updated date there was then a character_details table, which is basically a one-to-one mapping. So then I thought ok give character a 1-1 link to character_details and vice versa, however then it seemed to have lots of problems dealing with the adding of character and character detail models.

Now I have an issue where I cannot find the best way to map the "character_abilities" table.

The idea is each character will have a score for each ability, so lets say we have 6 ability types, they will have a base score and then an additional bonus. As each character has to have a value for each ability I thought the below would be the best type of table layout:

character_id, ability_type, base_score, misc_bonus

The key being a composite for character_id, ability_type, so that way there is no need for a surrogate key.

Ideally I would want to be able to just get a list/dictionary of these abilities for a given character, so in the web page I would show all abilities and relevant scores, then update them all together.

The problem for me stems from the fact that I have a character_id, but to map the character_abilities I need to tell it that its a composite key. Maybe i'm misunderstanding the way this would work, but as I want all abilities for a given character as a model so I can update it independently, without having a base character model...

Here is the SQL that I am ideally wanting to generate:

SELECT * FROM character_abilities WHERE character_id = ?

Then the update would be something like:

UPDATE character_abilities SET ... WHERE character_id = ? AND ability_type = ?

That would be fine as I have the ability type after I have done the first select.

Anyway does anyone have any advice on this stuff, as I dont want a huge hierarchy, im keeping the character model lean so everything can link to it (hence allowing everything to update itself without knowing about everything else), but it just seems to be giving me a headache...

Any help would be brilliant...