Chad,
> I am trying to come up with an efficient table design
> that describes a fantasy character that meets the
> following criteria:
<grin> Believe it or not, this is the first "D&D" question I've seen on
this list.
> 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,
> );
I do something similar a lot with User Defined Fields. Generally for
UDFs I use a TEXT field to hold the data, setting up something like
this:
CREATE TABLE udfs (udf_id SERIAL PRIMARY KEY,udf_format VARCHAR(30),udf_validate TEXT,udf_list INT
FOREIGNKEY udf_lists (list_id)
);
Where udf_format is a builtin or custom data type (INT, BOOLEAN, money,
NUMERIC, TEXT, phone, e-mail, etc.), and udf_validate is a regexp to
additionally validate the value.
Based on the information on this table, you can write a custom function
which formats each attribute as it comes out of the table based on the
reference table.
Hope that helps, half-elf!
-Josh