Dynamically creating fields
Hi all I have been searching for a method (and here is where I am stumped at the terminology to search for!) to dynamically add properties to some record. Let me explain: In a CRM system, where you have a number of contacts (people) you would need the user to create new attributes for them (such as daughter's name or some such). You dont want to create a table that already has that column, but you want to design a database model that can support dynamically (i.e. the user doing it not a developer) adding columns. I would prefer if the underlying database structure didnt change (as FarCry does when you add new attributes) There is also another issue that this would be a HUGE list of people (lets say 1million records) and we would be doing dynamic quieries such as SELECT * from bla where daugher = susan and birthday IN july and hasboughtGift = false (not real SQL) Any pointers? This must be a problem that has been addressed a number of times (I saw this in a shopping application called Intershop a while ago, the problem was doing efficient searches on these fields especially if you are looking for nulls) and also in spectra (that sidestepped the issue and stored it all as WDDX which made it suck at quieries) Any help or pointers apreciated! -- Mark Drew http://www.markdrew.co.uk/blog/ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231074 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Dynamically creating fields
I have been searching for a method (and here is where I am stumped at the terminology to search for!) to dynamically add properties to some record. snip Any pointers? This must be a problem that has been addressed a number of times Yes, certainly have addressed it a number of times. With a relational database the only options afaik are: 1. change the underlying structure 2. have a (huge) table containing the attribute values of each entity separately, with a lookup table of attributes 3. have a finite number of extra user-definable columns in each table (custom field 1, custom field 2 etc) We use 2 in our CMS. I refer to this as soft metadata but I don't know what anyone else calls it. It's the only solution that's generic enough for our purposes, because we provide an object-oriented data model with a kind of multiple inheritance. The performance implications need an awful lot of attention though. If solution 1 is an option, I'd consider it very carefully. Some software packages use option 3 rather successfully but it's basically a kludge, isn't it. Nick ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231076 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Dynamically creating fields
Thanks for that Nick I think all of these are kludes really, I am looking at Hibernate so which works with Java (the system we are developing is in Java but the same problem I am sure that has faced a llot of CF developers) The other way is to have multiple tables that can hold these simple values, but it starts getting messy with the joins etc so you could have one table with the main details, then tables with key/value pairs with the value being of a different type (int, varchar, double etc) But I think you are right and that performance degrades therearfter MD On 02/02/06, Nick de Voil [EMAIL PROTECTED] wrote: I have been searching for a method (and here is where I am stumped at the terminology to search for!) to dynamically add properties to some record. snip Any pointers? This must be a problem that has been addressed a number of times Yes, certainly have addressed it a number of times. With a relational database the only options afaik are: 1. change the underlying structure 2. have a (huge) table containing the attribute values of each entity separately, with a lookup table of attributes 3. have a finite number of extra user-definable columns in each table (custom field 1, custom field 2 etc) We use 2 in our CMS. I refer to this as soft metadata but I don't know what anyone else calls it. It's the only solution that's generic enough for our purposes, because we provide an object-oriented data model with a kind of multiple inheritance. The performance implications need an awful lot of attention though. If solution 1 is an option, I'd consider it very carefully. Some software packages use option 3 rather successfully but it's basically a kludge, isn't it. Nick ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231078 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
