Re: Table Design Questions

> Hello,
>
> I am trying to come up with an efficient table design
> that describes a fantasy character that meets the
> following criteria:

Apologies for only dealing with the last part of your query - busy at the
moment. I'll try and follow up in more detail later.

> CREATE TABLE ATTRIBUTES (
> CHAR_ID INT PRIMARY KEY NOT NULL,
> ATTRIB_TYPE_ID INT NOT NULL,
> ATTRIB_VALUE INT,
> CONSTRAINT ATTRIB_TYPE_ID_FK FOREIGN KEY
> (ATTRIB_TYPE_ID) REFERENCES ATTRIB_TYPES
> (ATTRIB_TYPE_ID)
> );
>
> CREATE TABLE ATTRIB_TYPES (
> ATTRIB_TYPE_ID INT PRIMARY KEY NOT NULL,
> ATTRIB_TYPE VARCHAR(20) NOT NULL,
> ATTRIB_NAME VARCHAR(20) UNIQUE NOT NULL,
> );

> Please forgive my naiveté, but, what are typical
> solutions for dealing with enumerated types or sets?
>
> For example, a set of (Human | Elf | Dwarf | Gnome)
> where the ATTRIBUTES.VALUE could be any combination of
> the above.

In this case just multiple entries in the ATTRIBUTES table with the same
CHAR_ID and ATTRIB_TYPE_ID.

char01, race, human
char01, race, elf

(of course, I've used the text representation of the actual numbers above)

> I realize I could, in this case, think of
> ATTRIBURES.VALUE as a binary value where a character
> is both a Human & Elf (half elf) is (1100), but that
> just doesn't sit right with me. Is there a better way
> to do this? Perhaps by making ATTRIBUTES.VALUE an
> array? If so, would I be correct in assuming this
> would add a performance hit to searches?

If you're doing it properly, you probably want to be able to have
something like (human 75%,elf 25%) which would mean adding a "percentage"
or "multiplier" column to your ATTRIBUTE table. Or you could classify the
different races/species with their own attribute type. So - you'd either
have

ATTRIBUTES (with new column)
==========
char01, race, human, 0.75
char01, race, elf, 0.75

or

ATTRIBUTES (existing columns)
==========
char01, ishuman, 75
char01, iself, 25

You might want the extra column anyway, then you could have:

char01, shortsword, attack, 40
char01, shortsword, parry, 30

> (And please don't tell me to just add half elf to the
> set :).

Of course, you could just add half-elf to the set ;-)

- Richard Huxton