> 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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
