Message-ID: <1598.192.168.1.16.1042211548.squirrel@mainbox.archonet.com>
In-Reply-To: <20030110090815.29853.qmail@web11607.mail.yahoo.com>
References: <20030110090815.29853.qmail@web11607.mail.yahoo.com>
Date: Fri, 10 Jan 2003 15:12:28 -0000 (GMT)
Subject: Re: Table Design Questions
From: dev@archonet.com
To: "Chad L" <abditus@yahoo.com>
Cc: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain;charset=iso-8859-1
Importance: Normal

> Hello,
>
> I am trying to come up with an efficient table design
> that describes a fantasy character that meets the
> following criteria:

Apologies for only dealing with the last part of your query - busy at the
moment. I'll try and follow up in more detail later.


> 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,
> );


> 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.

In this case just multiple entries in the ATTRIBUTES table with the same
CHAR_ID and ATTRIB_TYPE_ID.

char01, race, human
char01, race, elf

(of course, I've used the text representation of the actual numbers 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?

If you're doing it properly, you probably want to be able to have
something like (human 75%,elf 25%) which would mean adding a "percentage"
or "multiplier" column to your ATTRIBUTE table. Or you could classify the
different races/species with their own attribute type. So - you'd either
have

ATTRIBUTES (with new column)
==========
char01, race, human, 0.75
char01, race, elf, 0.75

or

ATTRIBUTES (existing columns)
==========
char01, ishuman, 75
char01, iself, 25

You might want the extra column anyway, then you could have:

char01, shortsword, attack, 40
char01, shortsword, parry, 30

> (And please don't tell me to just add half elf to the
> set :).

Of course, you could just add half-elf to the set ;-)

- Richard Huxton