http://ayende.comAyende @ RahienCopyright (C) Ayende Rahien 2004 - 2021 (c) 202660From http://www.fsinsider.com/developers/Pages/default.aspx --------------------------------------------------------------------------- "As Extensible As It Gets" The success of the Flight Simulator franchise is due in large part to the efforts of our third-party development community. Those efforts are made possible by our commitment to "extensibility." The main idea behind this concept is to provide customers with the power to customize their experiences. Over our 25-year history the names and faces on our team have changed, but our dedication to this concept has remained. --------------------------------------------------------------------------- I'm not from that world, but it makes sense to me that extensibility would be a key element of keeping games interesting to gamers and game platforms interesting to game developers and third parites... http://ayende.com/3498/multi-tenancy-extensible-data-model#comment35http://ayende.com/3498/multi-tenancy-extensible-data-model#comment35Sun, 17 Aug 2008 09:35:17 GMTWhy do you think they need extensible data support in those games? http://ayende.com/3498/multi-tenancy-extensible-data-model#comment34http://ayende.com/3498/multi-tenancy-extensible-data-model#comment34Sun, 17 Aug 2008 00:44:38 GMTWould be interesting to hear the thoughts of someone in the MS Flight Simulator / Game crews on the subject as I suspect they deal with this on a fairly regular basis. Anyone have contacts might want to consider asking them to chime in... http://ayende.com/3498/multi-tenancy-extensible-data-model#comment33http://ayende.com/3498/multi-tenancy-extensible-data-model#comment33Sun, 17 Aug 2008 00:41:22 GMTI don't know about 'great' as it's not an approach devoid of complexity itself, but there are enhancements to SQL Server's XML capabilities - the summary from the white paper on the enhancements: Microsoft® SQL Server™ 2008 builds on the extensive support for XML by extending support for XML schema validation and XQuery, and by enhancing the xml data type -XML Schema Validation Enhancements -Lax Validation Support -Full xs:datetime Support -Union and List Types -XQuery Enhancements -XML DML Enhancements http://www.microsoft.com/sqlserver/2008/en/us/wp-sql-2008-whats-new-xml.aspx I would say these enhancements make taking this approach a bit more reasonable proposition - if you're up for an XML-centric soloution to begin with. That said, I'm certainly of the mind data extensibility is a complex problem space with no simple solutions. All-in-all it almost seems more a matter of shuffling the complexity around to a form which is comfortable for any given developer rather than finding a solution which actually reduces it. http://ayende.com/3498/multi-tenancy-extensible-data-model#comment32http://ayende.com/3498/multi-tenancy-extensible-data-model#comment32Sat, 16 Aug 2008 22:27:25 GMTJoseph, I am not keeping up, what is great with XML in 2008? http://ayende.com/3498/multi-tenancy-extensible-data-model#comment31http://ayende.com/3498/multi-tenancy-extensible-data-model#comment31Sat, 16 Aug 2008 21:03:47 GMTAny comment on whether new SQL Server 2008 XML features changes anyone's mind relative to using XML for data extensibility? I still see it as reasonable approach compared to the complexity inherent in most of the alternatives. http://ayende.com/3498/multi-tenancy-extensible-data-model#comment30http://ayende.com/3498/multi-tenancy-extensible-data-model#comment30Sat, 16 Aug 2008 20:58:50 GMTFor custom fields, validation is also a consideration. I render my custom fields onto the web page dependant on their defintion. The best I have come up with for validation is to allow a regular expression to be associated with the field. Actually changing the ddl sounds like a bad plan. http://ayende.com/3498/multi-tenancy-extensible-data-model#comment29http://ayende.com/3498/multi-tenancy-extensible-data-model#comment29Sat, 09 Aug 2008 20:47:06 GMTI've used the "Key Value" option for a multi tenant e-commerce system (separate databases btw...). We have a CustomAttributeDefinition class which contains metadata about properties that can be set up against a particular type of entity. CustomAttributes are the actual realisation of the property for an entity. I would emphasise the importance of having a rich metadata layer for defining behaviour, rules and constraints for these custom properties. CustomAttributeDefinitions are entities in their own right. Subclasses of CustomAttributeDefinition define behaviour for a range of property types and contain things like descriptions and tooltips displayed to users when editing the values, constraints on the values that can be entered, ranges of values that can be selected etc. This supports things like: - Values (String, Boolean, Int, DateTime etc) - Lists of values (as above) - MultiChoice (select from range of options (defined in CustomAttributeDefinition), with rules for min / max number of selections - Measurement (amount / unit type) - Dimension (width, height, depth, unit type) A parallel hierarchy of CustomAttributeEditControls makes the editing experience within the UI a lot more pleasant, e.g. checkboxes for booleans (no more "Y/N, hmm or is it 1/0" in a text box), validation according to datatype etc. http://ayende.com/3498/multi-tenancy-extensible-data-model#comment28http://ayende.com/3498/multi-tenancy-extensible-data-model#comment28Sat, 09 Aug 2008 20:38:29 GMTIn most cases I saw, people tend to choose "Extending the customer table" (with all new columns either allowing NULL or having proper default or in more advances scenarios getting default values from certain queries ran once when creating the additional column), changing from INT to STRING language code would be a migration plan that feels "normal". Typically the migration would look like: creating another column with temp name, getting a query to set the new column values based on the corresponding old column values, and finally deleting the old column and renaming the new column to the old name if necessary. Clearly a down time thing, people sometimes avoiding by allowing the TWO columns to live probably forever, or until next version time (with acceptable idea of downtime) finally comes. http://ayende.com/3498/multi-tenancy-extensible-data-model#comment27http://ayende.com/3498/multi-tenancy-extensible-data-model#comment27Sat, 09 Aug 2008 15:49:52 GMTJeremy, how does this solution differs from having partitioned ATTRIBUTE table with [attribute_id, attribute_type_id, owner_entity_id, value] ? http://ayende.com/3498/multi-tenancy-extensible-data-model#comment26http://ayende.com/3498/multi-tenancy-extensible-data-model#comment26Sat, 09 Aug 2008 08:53:05 GMTAgain, it depends on the situation. I've worked with databases where the extra joins were feared as overhead but ended up being the optimal performing/scaling/maintaining/etc. option for a variety of reasons. This would appear to be yet another of those cases where everyone is right. ;) http://ayende.com/3498/multi-tenancy-extensible-data-model#comment25http://ayende.com/3498/multi-tenancy-extensible-data-model#comment25Fri, 08 Aug 2008 18:02:25 GMT@Ayende I agree! I meant to say I'm failing to communicate this to the rest of the team I'm working with. http://ayende.com/3498/multi-tenancy-extensible-data-model#comment24http://ayende.com/3498/multi-tenancy-extensible-data-model#comment24Fri, 08 Aug 2008 15:43:11 GMTJeremy , I understand what you are saying, but the cost of joins would be prohibitive to me. Adding ten columns == ten joins seems to be very costly http://ayende.com/3498/multi-tenancy-extensible-data-model#comment23http://ayende.com/3498/multi-tenancy-extensible-data-model#comment23Fri, 08 Aug 2008 15:34:29 GMT@Tobin, Huh? Why are you going with all this complexity? The flexibility you need is achievable, but the way you are going about is going to end in tears, from everything that I have seen http://ayende.com/3498/multi-tenancy-extensible-data-model#comment22http://ayende.com/3498/multi-tenancy-extensible-data-model#comment22Fri, 08 Aug 2008 15:25:30 GMTEric, Yes, I think it is alright to do this, yes. http://ayende.com/3498/multi-tenancy-extensible-data-model#comment21http://ayende.com/3498/multi-tenancy-extensible-data-model#comment21Fri, 08 Aug 2008 15:19:09 GMTApologies if I could have been clearer. The approach I mentioned involves programmatically generating a new table for each custom field, rather than programmatically issuing alter table statements against a single Customer_Extended (or what have you) table. It takes a bit more metadata tracking, because you need to keep mapping information around so that each custom field can be mapped to its generated table. That said, it can be a net win in cases where row counts are high and alter table statements would cause too much work in the db, and/or if the custom fields are often null but your database server's packing of unused columns in rows sucks (Oracle handles this far better than SQL Server, for example), and/or if you want to spread custom field tables (and/or logs and/or indices) around different spindles, etc. This model can be extended into multi-tenant scenarios quite nicely, and in fact gains a lot of leverage there especially you weren't planning on programmatically building and altering per-tenant Customer_Extended tables. http://ayende.com/3498/multi-tenancy-extensible-data-model#comment20http://ayende.com/3498/multi-tenancy-extensible-data-model#comment20Fri, 08 Aug 2008 14:16:11 GMTJeremy, Can you explain what this model is? http://ayende.com/3498/multi-tenancy-extensible-data-model#comment19http://ayende.com/3498/multi-tenancy-extensible-data-model#comment19Fri, 08 Aug 2008 12:46:29 GMTP.S -On the subject of unstructured data, I quite like the direction these guys are taking: http://strokedb.com WARNING: That link may result in you seeing Ruby code :) http://ayende.com/3498/multi-tenancy-extensible-data-model#comment18http://ayende.com/3498/multi-tenancy-extensible-data-model#comment18Fri, 08 Aug 2008 12:38:29 GMT@Ayende / @josh Thanks for the feedback. As you say, this model won't perform well. That was anticipated, and the solution is to export data to an optimized read-only runtime db to get realistic live web-site performance. It's the complexity that I'm most uncomfortable with. Delivering understandable & maintainable code is hard enough (for me at least!). I fear attempting to do it on top of a meta-layer will just makes it harder. The meta layer does add value, but it comes at a cost. Also, the exported live database will need to be mapped into c# objects to give us any kind of maintainable & understandable way of using the CMS data in web pages. So we need to write a mapping layer to generate the live db from CMS data, then generate/write objects, and then generate a mapping layer to get them out of the live db. Then we need to find a home for the small(ish) amount of business logic in the apps. I've been questioning if the oober flexible CMS back-end is worth the complexity of having all this extra work, but I'm not communicating well enough and haven't managed to collect and express my arguments successfully. I'll keep at it... Thanks again http://ayende.com/3498/multi-tenancy-extensible-data-model#comment17http://ayende.com/3498/multi-tenancy-extensible-data-model#comment17Fri, 08 Aug 2008 12:34:23 GMTOf the options you listed "Customer_Extended" is the one I've seen work best over the years. Of the options you _haven't_ listed, going fully table-generational on a per-tenant-per-custom-field basis has tended to work even better still, though occasionally at some cost due to the overhead of larger numbers of joins. That said, the join overhead has almost always been offset by better data locality, lower data page count, options for more effective indexing (and lower re-indexing overhead in high insert scenarios), etc. http://ayende.com/3498/multi-tenancy-extensible-data-model#comment16http://ayende.com/3498/multi-tenancy-extensible-data-model#comment16Fri, 08 Aug 2008 05:35:15 GMTOk, so now that we are on the same page on terminalogy =). My question is do you think it is a good idea to have an action by a customer (be it an admin or just a regular users) do something that ends up executing a DDL statement? I'm not saying that this is a bad thing, but I would approach it with some skepticism. http://ayende.com/3498/multi-tenancy-extensible-data-model#comment15http://ayende.com/3498/multi-tenancy-extensible-data-model#comment15Thu, 07 Aug 2008 23:58:37 GMTTobin, I agree with Ayende. I've been down the xml in the database several times. It's mostly not worked out. I have a specific case that I'm using it for now, but it's very limited and has nothing to do with extensibility. http://ayende.com/3498/multi-tenancy-extensible-data-model#comment14http://ayende.com/3498/multi-tenancy-extensible-data-model#comment14Thu, 07 Aug 2008 23:01:31 GMTThanks, from where I sit it's nice to see a dev as smart as yourself arriving at the same conclusions... Keep this topic going if you have the time, hopefully we can hire you for a code review. http://ayende.com/3498/multi-tenancy-extensible-data-model#comment13http://ayende.com/3498/multi-tenancy-extensible-data-model#comment13Thu, 07 Aug 2008 21:48:29 GMTEric, Even with salsforce, it is the tenant admin that does that. I as a standard user do not do that http://ayende.com/3498/multi-tenancy-extensible-data-model#comment12http://ayende.com/3498/multi-tenancy-extensible-data-model#comment12Thu, 07 Aug 2008 21:38:42 GMTI think that sort of customization (custom extension for a particular tenant) occurs a lot less frequent than the end user (the customer) adding fields to their entities. Something like Salesforce.com comes to mind as an example where this is done all over the place. http://ayende.com/3498/multi-tenancy-extensible-data-model#comment11http://ayende.com/3498/multi-tenancy-extensible-data-model#comment11Thu, 07 Aug 2008 21:19:13 GMTTobin, I would strongly recommend against this approach. It has horrible performance issues when you try to scale it, the complexity is huge, joining is not possible, etc. If you want unstructure data, don't use a DB http://ayende.com/3498/multi-tenancy-extensible-data-model#comment10http://ayende.com/3498/multi-tenancy-extensible-data-model#comment10Thu, 07 Aug 2008 21:17:34 GMTEric , Yes and no. The "end user" in this case is the tenant administrator, not just any random joe user. http://ayende.com/3498/multi-tenancy-extensible-data-model#comment9http://ayende.com/3498/multi-tenancy-extensible-data-model#comment9Thu, 07 Aug 2008 21:09:43 GMTMike D, For some reason I got a lot of contacts for multi tenancy stuff lately, and it _is_ an interesting subject. http://ayende.com/3498/multi-tenancy-extensible-data-model#comment8http://ayende.com/3498/multi-tenancy-extensible-data-model#comment8Thu, 07 Aug 2008 21:04:53 GMTChad, This is just a variation on the extended table, as far as I can see. http://ayende.com/3498/multi-tenancy-extensible-data-model#comment7http://ayende.com/3498/multi-tenancy-extensible-data-model#comment7Thu, 07 Aug 2008 21:02:37 GMTHonestly, I only glazed over the first in this series and skipped the second. I wasn't sure where you were going and didn't think it applied. But this one hit the "I've been there" button. I'm working with an app that has a rather complex (due to necessity) data structure. It supports user and 3rd party customizations down to the database level We have a well normalized schema, and allow what we call dynamic tables to extend our entities. For instance, our Contact entity is defined by data from a contact table and additional info in tables like Address, and Phones. The tables that define or extend the entity are tracked by an entity definition table that contains information regarding the system and extended tables that belong to an entity. I have an add-on that we develop for the product which adds two additional fields to the contact entity, which are stored in its own table. We maintain a contact view that rebuilds as needed based on information in the entity definition table. Our model classes are mutable types that are partly defined by mutable field descriptors. In our case, the app or addon does modify the database schema on the fly, but it is abstracted to help keep accessing the database straight forward. Maybe I'm misunderstanding, but it sounds like what you describe as the extended table approach. it works, and the performance in the database and framework is pretty decent/good. http://ayende.com/3498/multi-tenancy-extensible-data-model#comment6http://ayende.com/3498/multi-tenancy-extensible-data-model#comment6Thu, 07 Aug 2008 17:18:42 GMT