* * * Wizards Community Thread * * * -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Thread : Joe's Secret Project: Stage 1 Started at 08-13-05 06:29 AM by JoeNotCharles Visit at http://forums.gleemax.com/showthread.php?t=482179 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- [Post 1] Author : JoeNotCharles Date : 08-13-05 06:29 AM Thread Title : Joe's Secret Project: Stage 1 Ok, after much delay, I'm back to working on my Secret Project: the complete Mystaran Gazetteer. What I want is a nothing less than a complete reference to everything published about Mystara in all products. And I don't want it on a web page somewhere - I want it in a database so I can ask it questions like, "What products describe the Savage Coast?" or "What modules are set in the Principalities of Glantri?" or "In what countries is the Immortal Halav worshipped?" Tall order? Ok, it might be a bit of work, but far less than, say, Thorf's Secret Project. It's certainly doable. That's why I like Mystara, in fact - it's actually conceivable that I could read and catalogue everything published about it. Imagine doing that with The Forgotten Realms! Here's how I plan to do it. I've marked some things I'd like feedback on in bold. Technology: The Mystaran Gazetteer is a Postgresql database, and a web frontend written in Ruby on Rails to add new items or query it. You'll only have access to update or add to the database if you have a login and password, but everyone will be able to query it. (And I'll make all the source code and database contents public, so if you want to just set up your own copy you can do that too.) To start with I'll be doing all the updates myself, mainly because it's fun, but if I decide it's too much work for one person I'll start giving out access passes. What Gets Indexed: For now, I'll just be doing the RPG sourcebooks (not the novels or computer games) that are included in these lists: http://www.mystaranet.jamm.com/vaults/html/prodlist.html http://www.mystaranet.jamm.com/vaults/html/mystfaq.html http://www.geocities.com/TimesSquare/Dungeon/2967/prodlist.html http://home.flash.net/~brenfrow/ddindex.htm To my knowledge, that includes all BD&D and AD&D 2nd Ed products ever published. I will not (yet) be including 3rd Ed stuff or fan-created work. (If fan work is ever added, it will be clearly marked.) This is just to keep the amount of work down. I plan to do the rest someday. Each individual publication will be held in a Products table, with its name, a brief description and the system that it's written for (OD&D - for the original Blackmoor booklet and similar; BD&D; AD&D1; AD&D2; D&D3 - I don't plan to distinguish between 3.0 and 3.5). Yeah, But What Gets Indexed? All the "information" will be held in two tables: Concepts, which holds individual items of information, and "ConceptRefs", which links a concept to a product. For instance, the city of Specularum is a Concept, and its entry in the Concepts table will include its name, a brief description, and its location (Karameikos, another Concept). Specularum is described in both GAZ1: The Grand Duchy of Karameikos and the module B6: The Veiled Society (among others), so there will be two ConceptRef entries, one for each publication. As well as the Concept and the Product it's described in, each ConceptRef will have a "level" field describing how important this reference is. For instance, GAZ3: The Principalities of Glantri is the main resource for Glantri, so it will have a level of "Main"; Glantri is visited briefly in the course of Champions of Mystara, so this would have a level of "Minor". Concepts include: - Places: Planes, Planets, Continents, Regions (conceptual groupings of countries, such as the Known World and the Savage Coast), Countries, Provinces, Cities, Features (natural, such as mountains and lakes), and Structures (artificial, such as famous buildings or monuments). Each Place has a "parent" or "enclosing" Place, so we can get a full address. For instance Specularum would be Prime Plane (plane) -> Mystara (planet) -> Brun (continent) -> The Known World (region) -> The Grand Duchy of Karameikos (country) -> Specularum (city). Notice you can skip parts of the heirarchy; Specularum isn't in a Province (maybe it should be; I'll figure it out when I get around to indexing GAZ1). The Great School of Magic (a Structure) would be in Glantri City (a City), but Castle Amber (also a Structure) would be directly in The Principalities of Glantri (a Country) since it's not part of any city and we don't know what province it's in. Undecided: I plan to group seas in with Region, since "The Sea of Dread" and "The Known World" seem like about the same level of specificity to me. Should I add a separate Sea subtype instead? The ConceptRef for a place includes some Yes/No questions: HasMap (does this product include a map of the Place) and OnMap (does this product include a map of the enclosing Place, with the location of the Place marked?) For instance, the ConceptRef linking X2 and Castle Amber would have HasMap "true" since X2 contains a map of the castle, but OnMap "false" since it doesn't contain a map of Glantri with the castle's location marked. - People: PC's (ie. sample characters), NPC's (the vast majority) and Immortals. They will also have a location, which is filled in only if there is one place the character is almost always found. (Emperor Thincol is in Thyatis City 90% of the time, so he'd have it filled in; Bargle the Infamous would probably have the Black Eagle Barony - a province - as his location since it's his home base, although he can really be found anywhere in Karameikos and possibly beyond; Immortals could be found anywhere in the multiverse, so they wouldn't have a location at all.) ConceptRef's for people include a HasStats flag, to say whether the product contains game statistics or just a description. - Things: Artifacts (Immortal-level magic), MagicItems (notable mortal magic - not just every +1 sword), and NormalItems (especially valuable treasures, artworks and tomes, heirlooms, etc.) Like People, they have a Location if they are strongly associated with one place, and not otherwise. Also like People, their ConceptRef includes HasStats, which is "true" if the product gives game mechanics for them and "false" otherwise. - Race: This one's a bit trickier. Each Concept I've listed so far has a fixed list of subtypes (such as Artifact, MagicItem, NormalItem for Things). Race has a potentially unlimited number. Each notable clan or grouping will get an entry in the Race table, with their actual race as the subtype, and their home territory as the location. For instance, Clan de Belcadiz and Clan Erewan will have subtype Elf and location Glantri. Many, many products mention things like local orc tribes, so there will be entries like "Local Karameikos Orcs" as well. Undecided: Should I have separate subtypes for Orc, Goblin, etc, or a single Humanoids subtype? Also undecided: should I really bother to list Orc for every module that includes orcs as a wandering monster? True, it's information - yes, Orcs live in this area, because you can run into them - but there are an awful lot of them and it's not as interesting as an actual named Orc tribe. - Religion: Each Immortal is a subtype, and any piece of significant information relating to them gets its own entry. So, for Vanya, I would have one Person entry (name "Vanya", type "Person", subtype "Immortal", with a description of Vanya's personal and physical characteristics) and several Religion entries (name "Thyatian Vanya cult", type "Religion", subtype "Vanya", location "Thyatis"; name "Heldannic Vanya cult", type "Religion", subtype "Vanya", location "Heldann"). - Themes: Anything else that I feel is important or fun to note goes under themes. For instance, there would be a Criminals subtype for things like the Veiled Society and the Minrothad Thieves Guild; Nithia and Blackmoor subtypes; Thyatis and Alphatia subtypes for areas with Thyatian or Alphatian Conquests in their history; a subtype for famous Wars; etc. Undecided: I haven't decided exactly how to lay out these themes yet. Is a 3-level heirarchy enough (type "Theme", subtype "Nithia", entry "ruins on the highlands in Ylaruam") or should I set up a more generic heirarchy (Theme -> Ancient Civilizations -> Nithia -> highland ruins; Theme -> Ancient Civilizations -> Blackmoor -> The Radiance)? And how would I even do such a heirarchy? Whew. I'm getting tired. Tomorrow I'll post the database schema so you can see in more detail how this all fits together. In the meantime, here are some more things I haven't figured out yet: - What do I do about changes over time (the renaming of Karameikos, the creation of the Darokin crater - hell, Castle Amber returning)? - What about structures that exist in multiple places simultaneously (actually, I can't really think of any right now, but I'm sure I'll find one soon)? - Like I mentioned, I'll have a list of Products and a ConceptRef linking an entry in the Product table with an entry in the Concept table. What do I do about variant products? I don't want to have to repeat all the ConceptRefs for a product which is only slightly different. (This is a complicated question: the green-cover and orange-cover versions of B1 are clearly variants and their contents are almost, but not exactly, identical. What about GAZ1: The Kingdom of Karameikos and Karameikos: Kingdom of Adventure? What about Champions of Mystara and the original Voyage of the Princess Ark articles? What about B1-9: In Search of Adventure? I'm tempted to skip the whole mess and just have lots of ConceptRefs showing that the same concepts are described in the exact same ways in different products.) The Future, Conan? This is what I'm going to start out with. (I'll probably skimp heavily on the Themes for the time being.) There's more info that could be added. Off the top of my head, each Place could include a link to Thorf's map of that place, and each Product could include a cover scan. These can be easily added later. Anything else cool you think should be indexed? What's Finished I have a rudimentary UI which I've used to input B1 - In Search of Adventure, which was fairly easy since it doesn't have much meat. I'm currently thinking about how to lay out the UI better to make it easier to input the data for more complex products. I've also already noticed a few deficiencies in my database structure that I want to correct before I put in more data. That reminds me: What should I do about old modules like B1 that don't fit anywhere? It's put in Mystara by convention because it's part of the D&D line, but it doesn't mention any part of the Known World: it just has a few names of old adventurers and the concept of "barbarians to the north". I can make an educated guess that it goes in Karameikos (and the barbarians are Ylari tribesmen, which doesn't quite fit) or northern Ylaruam or Darokin (with the barbarians as Ethangars or Northern Reachmen), but based on the weirdness of the sample characters' names I'd like to put it in Ghyr: the tone seems to match XL1 quite well. (I believe B1-9 put it in Karameikos, but it also excised a lot of the background info that made it fit better with Ghyr - see what I mean about variants being hard to deal with?) So should I just make a decision and say "It's in Ghyr", or leave its location unknown? Anyway, my next step will be to add authentication and a viewer so I can make this public and let people look at it without worrying about them ruining all my hard work. Then I'll fix up the database, then extend the UI some more, and then start throwing data on the thing like a madman! -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- [Post 2] Author : Spellweaver Date : 08-13-05 08:13 AM Thread Title : Wow! Another great project! I am really looking forward to reading more about this! My own project is on stand-by until early September as I am handing in my thesis in less than 48 hours :w00t: and after that I am going to Greece for a while to relax and recover :ayyyy!: :-) Jesper -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- [Post 3] Author : eldersphinx Date : 08-13-05 02:04 PM Oooooh, humongous database project. A couple of comments - - For Races, consider two levels of entry, one general and one specific. This would allow DBM for both general stuff like 'humanoids' or 'lizardkind' and specific entries like 'elf', 'dwarf', 'orc', or 'goblin' as well. Humans are something of a wildcard here - an entry of Human could be a specific value under a general category of 'PC races', or general with individual human ethnicities ('Traladaran', 'Glantrian', 'Pearl Islander') as the specific slot. - Changes over time are pretty easily handled with StartDate and EndDate fields. General Torenal of Alphatia, for instance, might have StartDate 960 AC (or whatever, don't have books on my person for his birth year) and EndDate 1004 AC (due to his unfortunate demise during WotI). It's then pretty trivial to specify a year and screen out anything with a later StartDate or earlier EndDate. Data records that substantially change get new database entries, with possibly some pointer set up to note the successor (so "Principality of Blackhill", EndDate 1006 AC, might have a pointer set up to the Great Crater afterwards.) Good luck! :D -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- [Post 4] Author : maddog Date : 08-13-05 09:02 PM Thread Title : Are you sure you want to do this? The Mystaran Gazetteer is a Postgresql database, and a web frontend written in Ruby on Rails to add new items or query it. Wow.....ummmmm. Hmmmmmm. Are you sure you want to go to all this effort? I see some issues here. I don't think you have thought this completely through. Wouldn't the Mystara community be better off with a PDF of descriptive information (maybe some game stats) rather than a Postgresql database/Ruby on Rails? Someone would have to host it, right? That's alot of internet traffic. Also, you're not going to do the 3e/3.5e conversions (yet). I don't see how it's going help if that isn't done. The current version of the game (as much as I really don't want to say it) should be the target. What about the copyrights? (Darn it! I've opened such a bucket of worms!!!) :weep: --Ray. -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- [Post 5] Author : JoeNotCharles Date : 08-14-05 04:28 AM - For Races, consider two levels of entry, one general and one specific. This would allow DBM for both general stuff like 'humanoids' or 'lizardkind' and specific entries like 'elf', 'dwarf', 'orc', or 'goblin' as well. Humans are something of a wildcard here - an entry of Human could be a specific value under a general category of 'PC races', or general with individual human ethnicities ('Traladaran', 'Glantrian', 'Pearl Islander') as the specific slot. Yeah, I was thinking about that. I'm not sure how to get that to work with my current database schema, though. I'll post more details on that tomorrow. (Yeah, I know I said that yesterday.) Changes over time are pretty easily handled with StartDate and EndDate fields. Hey, good idea. I'll add those right away. Wow.....ummmmm. Hmmmmmm. Are you sure you want to go to all this effort? I see some issues here. I don't think you have thought this completely through. Yes, because I find it fun. The minute I find it more work then fun, I'll stop. Wouldn't the Mystara community be better off with a PDF of descriptive information (maybe some game stats) rather than a Postgresql database/Ruby on Rails? Someone would have to host it, right? That's alot of internet traffic. This isn't the big of a community. I think I can handle it, or easily find hosting that can. I already have an RTF file here (http://www.notcharles.ca:8080/~jnc/Mystara%20Products.rtf). I noticed while I was making it that I was entering a lot of information in multiple places, and it was hard to keep them in sync - sometimes I'd enter some data in one place and forget to enter it in another. (For instance, I wanted a list of "Elven Clans" so that I wouldn't have to search the whole thing to get a list of every elven clan mentioned, but I also wanted to list the local elven clans under each country.) So I realized that it would be much more efficient to make a database, and then I only have to input each clan once, and I can ask it either for a view of all elven clans or a view of everything it knows about a given country. If I want to publish a PDF (or, more like, an HTML page) with stats, I can just write a script to pull it from the database. But a nice relational database is clearly the right format to store this information at the root. Also, you're not going to do the 3e/3.5e conversions (yet). I don't see how it's going help if that isn't done. The current version of the game (as much as I really don't want to say it) should be the target. Help what? I'm not doing this for players of 3rd Ed, I'm doing this for me. I just thought the community might be interested. If you're not, go make your own project. What about the copyrights? Hmm, good question. I don't think that's a problem, since I'm just describing what each book contains, not duplicating the information. (I'm going to be noting whether each product contains stats or maps, for instance, but not actually listing those stats.) The idea is to give you a complete list of what you have to buy in PDF to get all the info on a certain topic - I can't see how that's not fair use. Thorf's maps may have a copyright problem, but I haven't made any provision to link them in yet, so I'll deal with that when the time comes. -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- [Post 6] Author : Thorf Date : 08-14-05 08:48 AM Undecided: Should I have separate subtypes for Orc, Goblin, etc, or a single Humanoids subtype? Also undecided: should I really bother to list Orc for every module that includes orcs as a wandering monster? True, it's information - yes, Orcs live in this area, because you can run into them - but there are an awful lot of them and it's not as interesting as an actual named Orc tribe. This really is a cool project. I'm not entirely sure if it's any less ambitious than my own project, though - you have a lot to work through! :D Regarding the wandering monster issue, doesn't your reference importance scale handle that already? A reference for a monster as a simple wandering monster is a "minor" (or less) reference. This isn't so important for orcs, as you said, but it should be done for the rarer or more interesting wandering monsters, probably. -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- [Post 7] Author : maddog Date : 08-14-05 08:56 AM Thread Title : Ah..... I see now. When you said, "Gazetteer" I was thinking "Orcs of Thar" or "Darokin". After reading through your RTF, I realize this is more like an ILS (Integrated Library System). It looks like you're going to make a full-text catalog with cross-references. Am I right? Just trying to understand the true nature of your project as it seems that I didn't get it right the first time. :) --Ray. -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- [Post 8] Author : Thorf Date : 08-14-05 10:59 AM Thorf's maps may have a copyright problem, but I haven't made any provision to link them in yet, so I'll deal with that when the time comes. As far as the copyright goes with my maps, or any other fan maps of Mystara for that matter, I really don't know where we stand. Obviously I have used the original copyrighted material as primary sources, and half of my work is making direct copies of those sources using my own art work (which itself is copied from/based on TSR art work), but I really don't know exactly what that means. However, it seems to me that, as dedicated fans of a dead world, it's not really an issue we need to deal with. As long as we aren't posting direct scans of the original TSR books, I don't think Wizards of the Coast will mind what we do. Especially since our motivations are not at all profit-based. -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- [Post 9] Author : JoeNotCharles Date : 08-19-05 10:17 PM Sorry for the delay. I've been trying on and off to do a nice diagram of the tables I'm planning to use, but I really have no graphical skills at all. I even found a nice free diagramming program (http://www.gnome.org/projects/dia/) and I'm still hopeless. So, instead, here's a text writup: sorry if it's hard to follow. All tables include an "id" column as primary key, which is a unique integer. Any column named foo_id is an integer which is a foreign key into another table. No column can be null unless marked "optional". Aside: any column named foo_ids points to one or more foreign keys - ok, you can't really do that. The real way to have a many-to-many relationship involves making an extra table, but that'd just clutter up the list. I you know anything about DB's, you know what I'm using a shorthand for, and if you don't, just pretend it means a list of id's instead of just one. As far as you know, that works fine. Products The first tables describe physical products. systems This table contains each different rule set I might be indexing products for. short_name (ie. BD&D, AD&D2) full_name: (ie. Basic Dungeons & Dragons, Advanced Dungeons & Dragons 2nd Edition) description: paragraph format description (optional) formats This table contains each physical format of product. name: (ie. box set, module, web download) description: paragraph format description (optional) products Finally the main product list! key: the brief product code number, or an informal equivalent (ie. X4, GAZ3, RC) name: the full name (ie. Master of the Desert Nomads, The Principalities of Glantri, D&D Rules Cyclopedia) description: paragraph format description (optional) format_id: the format of the product (ie. module, gazeteer, hardback) system_ids: all systems this product supports (ie. usually BD&D, but some modules will include BD&D and AD&D2) product_sources Places where a product is available online. I don't think I'll bother listing actual prices since they can change. I also won't bother making a complete list, since there are lots of PDF sellers - this table's entirely optional. product_id: the product available at this site url: self-explanatory is_free: a boolean describing whether it's a free download or a pay PDF So, for instance, B1 - In Search of the Unknown has: systems: id: 1 short_name: "BD&D" full_name: "Basic Dungeons & Dragons" description: "The "basic" line of the D&D/AD&D split, originally published in the Basic/Expert/Companion/Master sets." formats: id: 1 name: "module" description: "An adventure packaged in a folder with slipcover and stapled pages." products: id: 1 key: "B1" name: "In Search of the Unknown" description: "This package (a cover folder with maps and descriptive booklet within) forms a complete module for use with DUNGEONS & DRAGONS Basic Set. It is especially designed as an instructional aid for beginning Dungeon Masters and players, specifically created to enable new Dungeon Masters to initiate play with a minimum of preparation. In addition to descriptive and situational material, this module also includes special informational sections giving: background history and legends, listings of possible monsters and treasures and how to place them, a list of adventuring characters, tips on various aspects of play for the Dungeon Master, and helpful advice for starting players." format_id: 1 system_ids: 1 product_sources id: 1 product_id: 1 url: "http://www.rpgnow.com/product_info.php?products_id=1113&" is_free: false id: 2 product_id: 1 url: "http://www.rpgunited.com/product/default/tsr9023.html" is_free: false Of course, the "systems" and "formats" entries will be reused for other products, so there shouldn't be many of them; each product will get exactly one row in the "products" table, and possibly several rows in "product_sources". (Normally I'd just include one, but I did two to illustrate this time.) Ok, on to the concepts - oops, I'm late for something. Gotta go. -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- [Post 10] Author : JoeNotCharles Date : 10-27-05 07:20 AM Back again. After much tweaking, I'm pretty happy with the following schema. (This is pretty technical, and a lot of it restates what I've already written.) Tables Describing Products The rules system a product is written for (ie. Basic D&D, AD&D2, D&D 3.0/3.5): CREATE TABLE systems ( id serial NOT NULL, shortname character varying(80) NOT NULL, name text NOT NULL, description text ); ALTER TABLE ONLY systems ADD CONSTRAINT systems_pkey PRIMARY KEY (id); The format a product is written in (ie. "Module", "Box Set", "Hardcover", "Magazine Article", "Message Board Post"): CREATE TABLE formats ( id serial NOT NULL, name character varying(80) NOT NULL, description text ); ALTER TABLE ONLY formats ADD CONSTRAINT formats_pkey PRIMARY KEY (id); The products themselves: CREATE TABLE products ( id serial NOT NULL, "key" character varying(80) NOT NULL, name text NOT NULL, description text, authors text, format_id integer, system_id integer, edition character varying(80), year integer ); ALTER TABLE ONLY products ADD CONSTRAINT products_pkey PRIMARY KEY (id); ALTER TABLE ONLY products ADD CONSTRAINT "$1" FOREIGN KEY (format_id) REFERENCES formats(id); ALTER TABLE ONLY products ADD CONSTRAINT "$2" FOREIGN KEY (system_id) REFERENCES systems(id); Note that, as well as the standard "name" and "description" fields, each product has a list of authors (just a comma-separated string; I could split it out into a more structured format later, to let people search for items written by specific people, but I don't want to overcomplicate things yet), an edition and year of publication, and a format and rules system. I could also have added a "publisher" field (there are a few Judge's Guild books, and the new Blackmoor stuff from a different publisher) but as I don't plan to do anything but the TSR material to start with, I can always add this field later if there's a need for it. I also considered adding a boolean "is_official" flag, so I can add interesting non-canonical material from the Vaults, but that's for the far future. As for the "edition" and "year" fields, I don't intend to index every edition unless there are significant differences - such as the Green and Orange cover versions of Palace of the Silver Princesses, and maybe the Blue Box/Red Box versions of the Basic and Expert sets. This field is mainly to identify which edition I've chosen as the primary, most "canonical" version of a product (usually the latest). One drawback to the system_id field is that one product may have stats for more than one system, but I don't think any really do that except for the AD&D conversion notes in the Gazeteers (did the Almanacs have dual-statted entries?), and they're not that important. If it ever comes up, I can just list two separate "products" - for instance, "XZ3 - Made-Up Mystara Product (BD&D section)" and "XZ3 - Made-Up Mystara Product (D&D3 section)" - or go the more complicated route and move the system_id field to a separate table with a many-to-many mapping between systems and products. Finally, a word about conflicting/overlapping products. The main meat of the database will be the "concept_refs" table which tells what items of information are described in a given product by mapping an entry in the "concepts" table to an entry in the "products" table. A specific concept (such as a named NPC, say Jagger von Drachenfels) may have major references in several products (for instance, GAZ3 provides his main writeup and stats, and GAZ10 expands on his activities in the Broken Lands). Another product may duplicate most of this information (such as Glantri: Kingdom of Magic, which I haven't read yet but presumably rewrites much of the information in GAZ3). I'd considered a system of marking products as duplicates or variations of each other, but I've decided that the only feasible way to handle this is just to have a lot of duplicated concept_ref entries, in this case two each for every NPC, city, etc, described in GAZ3 and G:KOM. It's annoying, but it seems like G:KOM has enough changes that I can't just lump all its concept_refs together with GAZ3's. (There'll also be a table of conflicts listing which concept_ref's describe the same concept but in different ways. Except the location of Blackmoor to show up a lot in that table.) If anyone has a better way to handle products with lots of overlap, I'd love to hear it. Places to get a product: CREATE TABLE product_sources ( id serial NOT NULL, product_id integer NOT NULL, source text NOT NULL, is_free boolean ); ALTER TABLE ONLY product_sources ADD CONSTRAINT product_sources_pkey PRIMARY KEY (id); ALTER TABLE ONLY product_sources ADD CONSTRAINT "$1" FOREIGN KEY (product_id) REFERENCES products(id); "source" is a URL. I don't intend to be exhaustive - there are a lot of places to buy PDF's, and I'll probably just list one of them. I may add another field or table for "errata sources", for instance to link to the missing bits of ESD's that are going on the vaults. Tables Describing Concepts Categories of concepts: CREATE TABLE concept_types ( id serial NOT NULL, name character varying(80) NOT NULL, description text, parent_type_id integer ); ALTER TABLE ONLY concept_types ADD CONSTRAINT concept_types_pkey PRIMARY KEY (id); ALTER TABLE ONLY concept_types ADD CONSTRAINT "$1" FOREIGN KEY (parent_type_id) REFERENCES concept_types(id); Heirarchical categories don't encode very well in a relational database, but here's my best shot. There will be a tree of "concept_type" entries, and each concept will have a type. The main concept_type's are extremely broad: "Location", "Character", "Historical Event". These type's have NULL for parent_id, making them the base of the tree. Each has a bunch of subtypes - for instance, "Plane", "Planet", "Continent", "Country" and "City" are all types of "Location", so they'll each get a concept_type entry whose parent_id is the id of a more general concept_type, "Location". Some of these may have further subtypes - I may want to divide "Plane" into "Inner Plane" and "Outer Plane", for instance, but I haven't decided if that's a good idea yet. A concept will have a "concept_type_id" with the id of the most specific concept_type that encompasses it. The Grand Duchy of Karameikos is a Country, and also a Location (since all Countries are Locations). Unfortunately, SQL isn't very good at doing recursive queries like "give me all concepts whose type is Location or any subtype of that", so I'll have to write a higher-level interface to work with the concept_type tree - more on that later. This heirarchy of concept_types replaces the separate tables for each class of concept I described in earlier posts. It's much more flexible, so I like it. But, I'll still have to think hard about exactly how finely I should split of the concept_types, because it'll be hard to add a new layer after I've already created a lot of concepts and assigned concept_types to them. (For instance, if I add a distinction between Inner and Outer planes later, I'd have to go through and reclassify everything that's just marked "plane".) Last, a technical note: http://www.sitepoint.com/article/hierarchical-data-database has some notes on improving efficiency of hierarchical data stored in db's. It looks like I don't have to worry about this just yet - I can easily add a few columns later and then rewrite any higher-level logic that's being slow to make use of them. The concepts themselves: CREATE TABLE concepts ( id serial NOT NULL, name character varying(80) NOT NULL, description text, concept_type_id integer NOT NULL, location_id integer, start_year character varying(80), end_year character varying(80) ); ALTER TABLE ONLY concepts ADD CONSTRAINT concepts_pkey PRIMARY KEY (id); ALTER TABLE ONLY concepts ADD CONSTRAINT "$1" FOREIGN KEY (concept_type_id) REFERENCES concept_types(id); ALTER TABLE ONLY concepts ADD CONSTRAINT "$2" FOREIGN KEY (location_id) REFERENCES concepts(id); The most complicated table yet! Holds the name and description of each concept (for instance, a specific person, city or country), and a non-optional "type" (such as "Person", "City" or "Country"). Also 3 optional fields for locating this concept in space and time: start_year and end_year show the first and last years this concept is valid - the "Grand Duchy of Karameikos" ceases to exist in 1010AC, and the "Kingdom of Karameikos" replaces it. (It just occurred to me that this gets into the problem of duplicated information again - much of the references to the GDoK will be just as valid for KoK, but some won't. I'll think about adding more fields describing how concepts replace each other later.) location_id is a little ugly. It's supposed to hold a concept whose concept_type is Location (or some subtype). Characters who are mainly associated with a specific place could have this noted here, but its main use is for large structures and other concepts which don't move. Notably, a series of location_id's forms sort of an address: Specularam (concept_type_id City, location_id Karameikos) is part of the Grand Duchy of Karameikos (concept_type_id Country, location_id Known World), which is part of the Known World (concept_type_id Region, location_id Brun), which is part of Brun (concept_type_id Continent, location_id Mystara), which is part of Mystara (concept_type_id Planet, location_id Prime Plane), which is part of the Prime Plane (concept_type_id Plane, location_id NULL). Note that all these concept_types - City, Country, Region, Continent, Planet, and Plane - are entries in the concept_types table, with parent_id Location. This address list was the backbone of my original .rtf file version of Joe's Secret Project, which only went down to the country level, and it works great for locations but less well for other concepts, most of which would just leave location_id blank. However, it's ugly because there's no way I can enforce in the structure of the table that location_id has to point to a subtype of Location - I just have to put that check in higher-level logic, and trust that nobody goes behind its back and manipulates the database directly. Tables Describing References The importance of a reference: CREATE TABLE ref_levels ( id serial NOT NULL, name character varying(80) NOT NULL, description text, importance integer NOT NULL ); ALTER TABLE ONLY ref_levels ADD CONSTRAINT ref_levels_pkey PRIMARY KEY (id); Some products make only a passing mention of a concept, barely worth mentioning. Some describe the concept in detail. This table is a list of "levels" of reference - I haven't yet decided how detailed to get with this, but I will at least distinguish between "Major" references (which are the main writeup of a concept, such as the Gazeteer describing a country), "Minor" references (a passing mention) and "Contributing" references (a reference which provides a significant piece of information, such as a character's stats or a background detail). I've also pretty much decided to add categories for "Intro" (the first ever mention of a concept, no matter how little detail - such as the nation list in X1), "Setting" (for an adventure set in a country, even if it's fairly generic and doesn't give much detail about its surroundings) and "Listing" (for appearances of a concept in a chart or table, such as a monster race appearing in a wandering monster table - this provides no detail at all about the monster or country, but it's slightly useful for figuring out the overall range of the race, so it's useful to include this info in a very-low-importance category). The "importance" column is used to sort ref_levels from 1 (most important) to N (least important). The references themselves: CREATE TABLE concept_refs ( id serial NOT NULL, product_id integer NOT NULL, concept_id integer NOT NULL, description text, ref_level_id integer, is_on_map boolean, has_map boolean, has_stats boolean, page integer ); ALTER TABLE ONLY concept_refs ADD CONSTRAINT concept_refs_pkey PRIMARY KEY (id); ALTER TABLE ONLY concept_refs ADD CONSTRAINT "$1" FOREIGN KEY (product_id) REFERENCES products(id); ALTER TABLE ONLY concept_refs ADD CONSTRAINT "$2" FOREIGN KEY (concept_id) REFERENCES concepts(id); ALTER TABLE ONLY concept_refs ADD CONSTRAINT "$3" FOREIGN KEY (ref_level_id) REFERENCES ref_levels(id); The table says that a given product (product_id) has a reference to a given character, location, monster race, historical event, etc (concept_id) with a very brief description of what it says, and a ref_level_id categorizing the reference into major or minor level. (The description would be on the level of "Main reference for this country" or "Entire chapter devoted to this city" for a Major reference such as a Gazeteer, down to "Notes that pirates prey on shipping the bay" or "Race appears in Wandering Monster List" for a Minor aside in an adventure.) It also includes flags for whether the product contains a map of the concept (only valid for concepts of type Location or some subtype of that) or includes stats for it (valid for things like characters, races and magic items with game mechanics). For items with a fixed location_id, such as cities or countries, also includes an "is_on_map" field, to show whether the product includes a map of the surrounding area with the concept's location marked. (For instance, GAZ3 is the Major reference for Glantri City, and both has_map and is_on_map would be set to "true" since it includes a map of the city as well as a map of the country showing where to find the city. X2 would be the Major reference for Castle Amber, but only has_map would be true since there is no map of the surrounding area. is_on_map would be false, and has_stats would be NULL, since the idea of a castle having "stats" doesn't make any sense in this case, although it might if the module contained a Siege Machine scenario.) Conflicts: CREATE TABLE conflicts ( id serial NOT NULL, concept_ref1_id integer NOT NULL, concept_ref2_id integer NOT NULL, description text ); ALTER TABLE ONLY conflicts ADD CONSTRAINT conflicts_pkey PRIMARY KEY (id); ALTER TABLE ONLY conflicts ADD CONSTRAINT "$1" FOREIGN KEY (concept_ref1_id) REFERENCES concept_refs(id); ALTER TABLE ONLY conflicts ADD CONSTRAINT "$2" FOREIGN KEY (concept_ref2_id) REFERENCES concept_refs(id); If two refs to the same concept contradict each other, they go in this table. That's all for now - I just want to be sure I note these things as I find them, since it'd be hell to have to go back and audit all the data for them later. I'll figure out what to do with the conflict data later. Well, that's it. The main tables in this scheme are "products", "concepts" and "concept_refs", but each of them have a bunch of supporting tables. Comments are welcomed! The next step is to fill in the small, static tables (systems, formats, concept_types, ref_levels) by hand - I've already got this mostly done - and then build an easy interface in Ruby for inputting products, concepts and concept_refs, so that I can get on with the fun of actually building the encyclopedia. This interface will include a function to summarize what the DB knows about a concept in a nice pretty chunk of HTML I can paste onto the board, at which point I'll start doing what Thorf did with his maps and summarize a new bit of the world every day for you. -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- [Post 11] Author : JoeNotCharles Date : 10-28-05 02:57 AM I've decided to remove concept_types entirely - instead of having Country be a type with parent Location, and then Karameikos be an entry in a separate table with concept_type Country, I can just put them in one heirarchy in the Concept table: Location is a concept with no parent, Country is a concept with parent Location, and Karameikos is a concept with parent Country. So concepts now looks like this: CREATE TABLE concepts ( id serial NOT NULL, name character varying(80) NOT NULL, description text, parent_id integer, location_id integer, start_year character varying(80), end_year character varying(80) ); ALTER TABLE ONLY concepts ADD CONSTRAINT concepts_pkey PRIMARY KEY (id); ALTER TABLE ONLY concepts ADD CONSTRAINT "$1" FOREIGN KEY (parent_id) REFERENCES concepts(id); ALTER TABLE ONLY concepts ADD CONSTRAINT "$2" FOREIGN KEY (location_id) REFERENCES concepts(id); That means I can do things like say that the Fighter class is a concept, and the Rules Cyclopedia has the main stats and writeup for Fighters, and Paladin is a subclass of Fighter, and the Rules Cyclopedia etc. With the previous system, I'd either have to make Fighter a concept_type, in which case I couldn't link it to the Rules Cyclopedia through a concept_ref, or I'd make it a concept and be unable to make Paladin a subtype of it. -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- [Post 12] Author : Agathokles Date : 10-28-05 02:59 AM Nice and useful project! Note that something on these lines was done for Ravenloft (see http://www.kargatane.com/sotk/rldb/rldb.shtml ) on the then-official fan website, so it should be ok from the copyright point of view (at least, AFAIK no one raised any issue with the RL catalogue). -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Downloaded from Wizards Community (http://forums.gleemax.com) at 05-10-08 09:29 AM.