Table Design Questions - Mailing list pgsql-sql

Hello,

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

* Character "attributes" may somewhat frequently be
added and removed with time. (e.g.: I may decide later
on to add a boolean attribute CanSwim, or remove it
all together as code evolves in production.)

* To complicate things, attribute values may represent
numbers, boolean, or a selection of one or more
enumerated types.

* Attributes will be frequently searched against to
find characters that meet attribute criteria.

For the time being I have decided to structure the
Attributes table as something similar to the
following:

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,
 
);


So here are my Questions:

If ATTRIBUTES.VALUE could be an Integer, Boolean, or
Enumeration, what is a clean way to represent this in
the ATTRIB_TYPES table?  My initial thought was to
just set ATTRIB_TYPES.ATTRIB_TYPE to "INT", "BOOLEAN",
"ENUM", etc... and just leave it up to the front end
to worry about what these "types" mean and what is
considered valid data.

I was thinking that I could expand upon the
ATTRIB_TYPES table to include a MIN, MAX, and Enum
array columns in order to encapsulate what is
considered valid values/ranges for ATTRIBUTES.VALUE. 
Does this seem like a good use of inheritance off of
the ATTRIB_TYPES table?
--

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.

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?

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


Much appreciated,

Chad

__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com