From: "Josh Berkus" <josh@agliodbs.com>
Subject: Re: Table Design Questions
To: Chad L <abditus@yahoo.com>, pgsql-sql@postgresql.org
Date: Fri, 10 Jan 2003 09:32:52 -0800
Message-ID: <web-2309906@davinci.ethosmedia.com>
In-Reply-To: <20030110090815.29853.qmail@web11607.mail.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: 8bit

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