select a column named by another column in another table

Question

I am implementing a database that will back a role playing game. There are two relevant tables: character and weapon (plus a third table representing a standard many-to-many relationship; plus the level of each specific instance of a weapon). A character has multiple attributes (strength, agility, magic etc.) and each weapon has a base damage, a level (defined in the many-to-many association), and receives a bonus from the associated attribute of the character wielding said weapon (strength for clubs, agility for ranged weapons etc.). The effectiveness of a weapon must be derived from the three tables. The catch is that which column of the character table applies is dependent on the specific weapon being used.

My current paradigm is to perform two select queries, one to retrieve the name of the associated attribute (varchar) from the weapon table and then one - with the previously returned value substituted in - for the value of that attribute from the wielding character. I would like to replace this with a pure sql solution.

I have searched around the nets and found two other questions: Pivot on Multiple Columns using Tablefunc and PostgreSQL Crosstab Query but neither does quite what I'm looking for. I also found the postgres internal datatype oid [https://www.postgresql.org/docs/9.1/static/datatype-oid.html ], and was able to locate the oid of a specific column, but could not find the syntax for querying the value of the column with that oid.

Table schemeta:

create table character (
    id int primary key,
    agility int,
    strength int,
    magic int,
    ...);
create table weapon (
    id int primary key,
    damage int,
    associated_attribute varchar(32), --this can be another type if it'd help
    ...);
create table weapon_character_m2m (
    id int primary key,
    weapon int, --foreign key to weapon.id
    character int, --foreign key to character.id
    level int);

In my mind, this should be query-able with something like this (ideally resulting in the effective damage of each weapon currently in the player's possession.):

select m2m.level as level,
    weapon.associated_attribute as base_attr_name,
    character.??? as base_attr,
    weapon.damage as base_damage,
    base_damage * base_attr * level as effective_attr -- this is the column I care about, others are for clarity via alias
from weapon_character_m2m as m2m
join weapon on weapon.id=m2m.weapon
join character on character.id=m2m.character;
where m2m.character=$d -- stored proc parameter or the like

Most online resources I've found end up suggesting the database be redesigned. This is an option, but I really don't want to have a different table for each attribute to which a weapon might associate (in practice there are nearly 20 attributes that might be associated with weapon classes).

I have heard that this is possible in MSSQL by Foreign Key'ing into an internal system table, but I have no experience with MSSQL, let alone enough to attempt something like that (and I couldn't find a working sample on the internets). I would consider migrating to MSSQL (or any other sql engine) if anyone can provide a working example.

Answer 1

It sounds like you can just use the CASE statement. I know it is in MS SQL...not sure about PostgreSQL.

Something like (on my phone so just estimating the code ):

Select other fields,
    case weapon.associated_attribute
        when 'agility' then character.agility
        when 'strength' then character.strength 
        when ...
        else 0  --unhandled associate_attribute
    end as base_attr
from ...

The caveat here is that you will want your character attributes to be the same type which it looks like you do.

EDIT

I worked towards a view based on your feedback and realized that a view would use an unpivot rather than the case statement above but that you could use a function to do it using the CASE structure above. There are many flavours :-) MS SQL also has table-valued functions that you could use to return one attribute type for all characters. Here is the code I was playing with. It contains both a view and a function. You can choose which seems more appropriate.

create table character (
    id int primary key,
    agility int,
    strength int,
    magic int,
    --...
    );
insert character values (1,10,15,20),(2,11,12,13);

create table attribute_type (
    attribute_id int primary key,
    attribute_name varchar(20)
    );
insert attribute_type values (1,'Agility'),(2,'Strength'),(3,'Magic');

create table weapon (
    id int primary key,
    damage int,
    --associated_attribute varchar(32), --this can be another type if it'd help
    attribute_id int
    --...
    );
insert weapon values (1,20,1),(2,30,2);

create table weapon_character_m2m (
    id int primary key,
    weapon int, --foreign key to weapon.id
    character int, --foreign key to character.id
    level int);
insert weapon_character_m2m values (1,1,1,4),(2,2,2,5);
go

create view vw_character_attributes
as
select  c.id, a.attribute_id, c.attribute_value
from    (
        select  id, attribute_name, attribute_value
        from    (select id, agility, strength, magic from character) p      --pivoted data
        unpivot (attribute_value for attribute_name in (agility, strength, magic)) u        --unpivoted data
        ) c
join    attribute_type a on a.attribute_name = c.attribute_name
;
go

create function fn_get_character_attribute (@character_id int, @attribute_id int)
returns int
as
begin
    declare @attr int;

    select  @attr =
            case @attribute_id
                when 1 then c.agility
                when 2 then c.strength 
                when 3 then c.magic 
                --when ...
                else 0  --unhandled associate_attribute
            end
    from    character c
    where   c.id = @character_id;

    return  @attr;
end

go
select * from vw_character_attributes;

select m2m.level as level,
    at.attribute_name as base_attr_name,
    ca.attribute_value as base_attr,
    dbo.fn_get_character_attribute(m2m.id, weapon.attribute_id ) function_value,
    weapon.damage as base_damage,
    weapon.damage * ca.attribute_value * level as effective_attr -- this is the column I care about, others are for clarity via alias
from weapon_character_m2m as m2m
join weapon on weapon.id=m2m.weapon
join vw_character_attributes ca on ca.id=m2m.character and ca.attribute_id = weapon.attribute_id
join attribute_type at on at.attribute_id = weapon.attribute_id
--where m2m.character=$d; -- stored proc parameter or the like