Reply to thread

Message: <blockquote data-quote="Janx" data-source="post: 6210269" data-attributes="member: 8835">Bear in mind, you have not failed to communicate or explain.If I had a Masters, my thesis would be in dynamic data definition systems.  I have designed and built several of these things from scratch.I know EXACTLY what you are trying to build and have at least 3 different solutions to it.The failure is mine, because I am speaking in programming/database design terms.  So my information is for another programmer, not a normal person such as yourself.What my concern is, is that if my verbiage confused you and you hadn't been explainified by your developer already, then your developer may not fully appreciate what is being asked of him, or of how he intends to do it (because he too should be confusing you, or have already made my technobabble make sense).The details of what user owns a Table (like a spell list), what ruleset it applies to or how they are categorized is important, but trivial to implement.What it comes down to is that at some point, a user wants to make a new Table (say for himself) that is inherently unrelated to anybody else's Tables (what you call databases, but lets stick with the more correct term).Now it seems obvious that in a normal application, when I need to store patients, I make a Table called Patients and then I add fields like Firstname, Lastname, DOB, Address, PhoneNum, etc.You would think that's all it takes to do so in this system for me to get a table called Janx's PF Spells.But it isn't.  Depending on the permissions on the server, EN's running instance of the site may not be allowed to make database structure changes.  That kind of thing is important for preventing hacking, and database structure changes would be done seperately through an administrator account that the EN site itself doesn't run as.So Design Pattern #1 may or may not be off the table of "let the UI code create new database structures on the fly"All is not lost, there's at least 2 other ways to skin this cat (I am always on the hunt for more ways to do dynamic data).Regardless of which Design Pattern is used, you're going to need a Table of Tables.  Something that itself tells us what tables we have, who owns them, what category and rule set they belong to.  That's actually pretty simple, but realize it is going to need to exist, and it is going to help you with the other design patterns.Since Morrus likes to call these Databases, let's call our Table that (remember, I am speaking in SQL/relational database terms).DatabasesDatabaseID (primary key)Owner_UserID (who owns this, foreign key to EN's User table)RuleSet (text name of the ruleset, or make it RuleSetID and FK it to a RuleSets table listing the rule sets we support)Category  (text name of the category, or make it CategoryID and FK it to a Categories table listing the rule sets we support)IsPublic (boolean flag so the owner can expose it to the public or hide it while he constructs it)Once you have that, you now have a list of Databases in the system.  From here, we can make a new Table to define what fields are on a given Database (ex the 3e Spells database).  The actual details of what the table is called, or what fields are in this alleged Databases table is not of great concern, as its a matter of talking to Morrus to see how he wants to control access, rank/categorize these things and then adjusting accordingly.  The meat is in the other tables, to achieve the core objective of letting users define new data structures on the fly.DBFieldsDBFieldID (primary key)DatabaseID (foreign key to Databases table, tells us a given row in this table defines a field on that specified Database)FieldName (what we call this field on the user's screen, ex: Name, Level, School)DataType (how to present the field on screen as a 50 char text box, a big WYSIWYG box, numbers only, etc)Now with Design Pattern 1, you'll still need the Databases table, as it is going to tell you what all the generated tables in the system are for (and control access, since you see it says who owns it).  You won't need DBFields because the actual table generated by the UI will signal what the columns are and their datatype by defining them as actual tables in SQL terms.Without Design Pattern 1, we rely on more complex solutions.  I actually like Design Pattern 1 as we get better SQL performance querying against a single table.  however, because all the data is spread across lots of tables, if I wanted to search EVERYTHING for Level 1 Spells, that'd be harder, as Janx's Spells are in the Janx'sSpells table, and Morrus's are in another table.Enter Design Pattern 2, also known as over-engineering, but aside from performance concerns for large data sets, it is technologically perfect (meaning it's a good design, but it sucks to run it)We create an Item table to define a single instance (Magic Missile in the Janx's Spells Database).  the items table will act as an anchor point for all the kinds of things in the list (so if there's 100 first level spells, there will be 100 rows in this table).ItemsItemID (primary key)DatabaseID (foreign key to Databases table)Then, we need an ItemFields table to store the actual field values for the given ItemID (ex, ItemID1 represents Magic Misssile in my Database of DatabaseID 23).ItemFieldsItemFieldID (primary key)DBFieldID (foreign key to DBFields table which tells me what this Itemfield actually is, ex: Spell Level)ItemID (foreign key to Items table, which tells me this ItemField is for the specific ItemID 1 which represents magic missle)ItemValue (blob datatype, can store anything.  the UI will enforce data type by looking at the related DBField's DataType telling it to display a short text box, a WYSIWYG box, or a drop -down list in the item editor)those are 2 design patterns.  I gotta dash to a meeting.  But this should give a sense of how complex this kind of thing is if you've never built one before.The guy building the system should be able to read my verbiage and agree, or propose an alternative that solves the problem.  If he can't follow it what I said, be concerned that he hasn't fully grokked the business requirements.</blockquote>

Verification