Re: Table Design Questions

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