pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Table Design Questions 3+ messages / 3 participants [nested] [flat]
* Table Design Questions @ 2003-01-10 09:08 Chad L <abditus@yahoo.com> 0 siblings, 2 replies; 3+ messages in thread From: Chad L @ 2003-01-10 09:08 UTC (permalink / raw) To: 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Table Design Questions @ 2003-01-10 15:12 dev@archonet.com parent: Chad L <abditus@yahoo.com> 1 sibling, 0 replies; 3+ messages in thread From: dev@archonet.com @ 2003-01-10 15:12 UTC (permalink / raw) To: Chad L <abditus@yahoo.com>; +Cc: pgsql-sql > 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Table Design Questions @ 2003-01-10 17:32 Josh Berkus <josh@agliodbs.com> parent: Chad L <abditus@yahoo.com> 1 sibling, 0 replies; 3+ messages in thread From: Josh Berkus @ 2003-01-10 17:32 UTC (permalink / raw) To: Chad L <abditus@yahoo.com>; pgsql-sql 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-01-10 17:32 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2003-01-10 09:08 Table Design Questions Chad L <abditus@yahoo.com> 2003-01-10 15:12 ` dev@archonet.com 2003-01-10 17:32 ` Josh Berkus <josh@agliodbs.com>
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