Thread: Table Design Questions
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
> 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
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 FOREIGNKEY 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