I agree that modifying the database schema at runtime is usually a bad idea, but having implemented more EAV systems than I can remember, I'm starting to think that maybe we shouldn't treat the schema as something holy. Or, at least we could maybe have a relaxed attitude for one or a couple of tables in our database, which we treat differently. The performance gain and reduced query complexity can be worth it. The trick is just to be careful.
Of course, a more unstructured storage of "user defined entities" is also feasible, e.g storing the objects as a serialized blob (or XML). Indexing and querying can be dealt with. Maybe not by the DBMS, but by adding external indexing, e.g with Lucene. /G On Wed, Oct 13, 2010 at 7:42 AM, pvginkel <[email protected]> wrote: > I would think that this strategy would make upgrading the databases to > a new version of your application a living hell. What happens when a > new version of your application adds a field to a table that a client > added himself? > > What do you need this facility for? If it's just to allow your clients > to add free form information to tables, adding a generic mechanism, > similar to EAV [http://en.wikipedia.org/wiki/Entity-attribute- > value_model] to your database schema would probably be a better idea. > > It looks like "Implementing and indexing User Defined Fields in an SQL > DB" [http://stackoverflow.com/questions/1600811/implementing-and- > indexing-user-defined-fields-in-an-sql-db] describes what you're > trying to do. > > This would also solve the issue of your objects being out of date with > your schema. > > On Oct 12, 5:50 am, foxprorawks <[email protected]> wrote: >> Hi, >> >> We are currently evaluating various technologies and designing our new >> product. >> >> Our new product will include multi-tenancy. Each customer will be >> supplied with a standard database, but the system will allow users to >> modify the database (by adding or removing fields from existing >> tablesor adding totally new tables). Each customer will have their >> own separate database. >> >> The database modification will be done via a web interface. >> >> We are looking at MVC2 and NHibernate 2. >> >> This is the first time we've used either technology, so it's a steep >> learning curve for us. >> >> We've read some interesting ideas, particularly on the Ayende site: >> >> http://ayende.com/Blog/archive/2009/04/11/nhibernate-mapping-ltdynami... >> >> One question we have is, can the data model be easily updated at >> runtime to match the new fields added by customers? >> >> Also, how do we get the application to use separate data models for >> each customer (although each customer will have a separate database, >> they will all be served by the same application which will determine >> which database to connect to)? >> >> Regards. >> >> Matt. > > -- > You received this message because you are subscribed to the Google Groups > "nhusers" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/nhusers?hl=en. > > -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
