Upgrade your account to a Community Supporter account and remove most of the site ads.

Reply to thread

Message: <blockquote data-quote="javapadawan" data-source="post: 1120054" data-attributes="member: 1444">Oooo, data modeling! Can I play? <img src="https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f642.png" class="smilie smilie--emoji" loading="lazy" width="64" height="64" alt=":)" title="Smile    :)"  data-smilie="1"data-shortname=":)" />I've been working on a new model for the data in my spell database, primarily because at first I had all the spell data denormalized into one table, with a setup much like The Dood's, but I now want to add a feature to my program that sorts by spell level, and also allows for adding custom descriptors, domains, classes, etc.What I have thus far looks like this, and I'd love to hear any comments or suggestions. Additionally, if you like this design at all, please feel free to use or improve upon it for yourself:spell-----spell_id (AutoNumber)name (Text)components (Text)casting_time (Text)spell_range (Text)effect_type (Text)effect (Text)duration (Text)saving_throw (Text)spell_resistance (Text)short_desc (Memo)long_desc (Memo)*I separated effect type from effect hoping to make searching for spells which have a Target as opposed to Area effect spells easier.attribute_type--------------attribute_type_id (Number)attribute_type (Text)*Examples of attribute types would be: School, Subschool, Descriptor, Class, Domain, Source. ID is not AutoNumber since these aren't created on the fly and can be used as constants for grouping attributes.attribute---------attribute_id (AutoNumber)attribute_type_id (Number, foreign key: attribute_type)attribute (Text)information (Memo, nullable)abbrev (Text, nullable)*So, for example, this is where I'd list all the spell Schools (Conjuration, Abjuration, etc.) with an attribute_type_id matching that of the School attribute_type. The information field is for holding miscellaneous data, like granted powers for the domains. The abbrev field is what is sounds like it is, so for example, the attribute Sorcerer/Wizard (attribute_type Class) would have Sor/Wiz in it's abbrev field.spell_has_attribute-------------------spell_has_attribute_id (AutoNumber)attribute_type_id (Number, foreign key: attribute_type)attribute_id (Number, foreign key: attribute)spell_id (Number, foreign key: spell)value (Number)*This table, then, is what ties the attribute to a particular spell, and each spell may have none, one, or many of any particular attribute type. The value field can hold the actual spell level. For example, Acid Fog would have one record in spell_has_attribute with an attribute_type_id matching that of the Class attribute_type, whose attribute_id would match that of the Sorcerer/Wizard attribute, and whose value would be 6 (the level a Sorcerer or Wizard could cast the Acid Fog spell at). Likewise, there would also be a record for that spell tying it to the Domain attribute_type and the Water attribute, with a value of 7.I'm still on the fence about whether or not component types, like material, verbal, XP, etc., should be attributes rather than plain text. Anyway, if you read this far, thanks, and I'd welcome any feedback, even if it questions my sanity. <img src="https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f642.png" class="smilie smilie--emoji" loading="lazy" width="64" height="64" alt=":)" title="Smile    :)"  data-smilie="1"data-shortname=":)" /></blockquote>

Verification