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 ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
