pg.ddx.io pgsql-sql@postgresql.org mailing list archive help / color / mirror / Atom feed From: Chad L <abditus@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Table Design Questions
Date: Fri, 10 Jan 2003 01:08:15 -0800 (PST)
Message-ID: <20030110090815.29853.qmail@web11607.mail.yahoo.com> (raw )
Hello,
I am trying to come up with an efficient table design
that describes a fantasy character that meets the
following criteria:
* Character "attributes" may somewhat frequently be
added and removed with time. (e.g.: I may decide later
on to add a boolean attribute CanSwim, or remove it
all together as code evolves in production.)
* To complicate things, attribute values may represent
numbers, boolean, or a selection of one or more
enumerated types.
* Attributes will be frequently searched against to
find characters that meet attribute criteria.
For the time being I have decided to structure the
Attributes table as something similar to the
following:
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,
);
So here are my Questions:
If ATTRIBUTES.VALUE could be an Integer, Boolean, or
Enumeration, what is a clean way to represent this in
the ATTRIB_TYPES table? My initial thought was to
just set ATTRIB_TYPES.ATTRIB_TYPE to "INT", "BOOLEAN",
"ENUM", etc... and just leave it up to the front end
to worry about what these "types" mean and what is
considered valid data.
I was thinking that I could expand upon the
ATTRIB_TYPES table to include a MIN, MAX, and Enum
array columns in order to encapsulate what is
considered valid values/ranges for ATTRIBUTES.VALUE.
Does this seem like a good use of inheritance off of
the ATTRIB_TYPES table?
--
Please forgive my naiveté, but, what are typical
solutions for dealing with enumerated types or sets?
For example, a set of (Human | Elf | Dwarf | Gnome)
where the ATTRIBUTES.VALUE could be any combination of
the above.
I realize I could, in this case, think of
ATTRIBURES.VALUE as a binary value where a character
is both a Human & Elf (half elf) is (1100), but that
just doesn't sit right with me. Is there a better way
to do this? Perhaps by making ATTRIBUTES.VALUE an
array? If so, would I be correct in assuming this
would add a performance hit to searches?
(And please don't tell me to just add half elf to the
set :).
Much appreciated,
Chad
__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
view thread (3+ messages) latest in thread
Message-ID: <20030110090815.29853.qmail@web11607.mail.yahoo.com>
Permalink: ../20030110090815.29853.qmail@web11607.mail.yahoo.com/
Also on: postgresql.org/message-id/20030110090815.29853.qmail@web11607.mail.yahoo.com
copy link · copy postgr.es
reply 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: abditus@yahoo.com
Subject: Re: Table Design Questions
In-Reply-To: <20030110090815.29853.qmail@web11607.mail.yahoo.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