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 FOREIGN KEY 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