From: Josh Berkus <josh@agliodbs.com>
To: Chad L <abditus@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Re: Table Design Questions
Date: Fri, 10 Jan 2003 09:32:52 -0800
Message-ID: <web-2309906@davinci.ethosmedia.com> (raw)
In-Reply-To: <20030110090815.29853.qmail@web11607.mail.yahoo.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, abditus@yahoo.com
Subject: Re: Table Design Questions
In-Reply-To: <web-2309906@davinci.ethosmedia.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox