From: | Chad L <abditus(at)yahoo(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Table Design Questions |
Date: | 2003-01-10 09:08:15 |
Message-ID: | 20030110090815.29853.qmail@web11607.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Radu-Adrian Popescu | 2003-01-10 09:13:01 | Re: SQL function parse error ? |
Previous Message | Tom Lane | 2003-01-10 04:53:42 | Re: insert rule doesn't see id field |