Hey Rich, One piece of advice I can certainly give you is that your domain model (CFCs) should NOT be based off of, or dependent on, your data model at all. So if you decide to use 4 tables or 1 table, the main actors and general structure of your components should be nearly identical. What would change are the queries in the back-end that populate your components. In one case the query would be simple and in the other a more complex 4-table join - but the end result will be the same.
With that said, let's first consider the options for your data model - either one table per lookup or four tables for unlimited lookups. I am not a DB expert so please take any suggestions with a thick grain of salt but my first impression is that you had it right the first time. Although it is much simpler and easier to do it by changing the structure of your database, I think you might bump into some serious problems later on. Here are some thoughts: - Flexibility: What happens if someone decides to delete a column? Do you remove it and all its historical data forever? What if you wanted to just hide it or do a soft-delete so that they can undelete later on? With the 4-table schema you can add as much meta-data per column as you like: hidden, read-only, soft-delete, etc. The other way you can't, or if you do, you lose the simplicity that made you go with hat solution in the first place. - Performance: Don't bet your house on this, but I am pretty sure that RDMS's are MUCH slower when you change their structure rather than their contents. They may have to re-index, most-certainly write to disk and other heavy low-level stuff. If people are going to be adding or deleting columns and entire tables relatively often, you might bump into serious disk fragmentation and slowness issues. - Stability: Again, I am no expert, but I have a feeling that you will be sacrificing stability if you are constantly playing with the DB structure. RDMS's are really optimized to add/remove rows, not columns and entire tables. These activities could be hitting the disk, system tables and meta-data tables too often for comfort. - Roll Back: Is it possible to roll back a bad transaction if it is structural in nature? Is it possible to even have 'transactions'? Is it easy/possible to make backups/retores of the DB? What about incremental backups/restores? - Full-text search: If you ever needed powerful full-text searching on any of your lookups, your simple solution just got a whole lot more complicated. I know in MS-SQL anytime I have to make a change to a table that is full-text indexed, I have to manually re-created all indexes and related views and such. and since you are doing it at run-time, all that will have to be coded into your app and executed every single time someone adds/removes a column. Not to mention that all data will have to be re-indexed on every change - which will take atleast minutes and maybe hours. - Validation: How are you going to validate that a certain object doesn't already exist? Like if someone wanted to add "GP" again, how will you check whether it exists or not? Instead of just querying the "lookups" table you will have to query your specific database's special system meta-data tables - or maintain and synchronize a "lookups" table anyway. Same goes for the columns and everything else. These are some questions that I got off the top of my head, I am sure other questions/issues could come up later on. I could be off on some or all of these but I just wanted to throw it your way to think about it. You may very well know much better than I. But I have a feeling that the safer and more 'proper' way to do it is to model it how you originally intended, and in essence how it actually is. Your system is not a system that manages GPs, it is a system that manages two-dimensional representations of data - so build it as such :-) I don't have time right now to go into ideas about structuring the CFCs, but reply with an update and I will write back tomorrow. There are some very interesting ways to approach this. When you try and think of a solution, completely forget about the concept of GP or NURSE or whatever - they have nothing to do with your system really - they are just simple values of properties, not properties or objects themselves. Good luck! Baz On Wed, Sep 17, 2008 at 9:31 AM, Alan Livie <[EMAIL PROTECTED]> wrote: > I'm still not exactly sure of the schema choice for the app but one thing I > would suggest is forget about beans, services, factories, dao's and gateways > to start with. > Just create your 2 objects, Lookup and LookupContents and get them > interacting with each other. You can refactor out into bean, service, > factories and gateways later once you're happy your design actually works. > It sounds like there will be many design changes as you build this so you > don't want 8+ objects to start with that will probably all go through big > changes, just go with one or two and take it from there. > Its also a good idea to try some unit testing as you go so you can refactor > with more confidence. > > Alan > > ----- Original Message ---- > From: cs01rsw <[EMAIL PROTECTED]> > To: CFCDev <[email protected]> > Sent: Wednesday, September 17, 2008 12:45:22 PM > Subject: [CFCDEV] Re: cfc scenario help > > > Hi Baz, thanks for the reply > > yes the goal of the system is to allow the users to model anything. > they can create their own lookups, as you said surgeons, hostpitals > etc.... our system stores the data not in a grid format but in the 4 > tables. > > so the lookupColumns will store just the names of the columns and the > lookupvalues just store the values, then the 4th lookupdata table will > store the data for a value and a column - so it is acting like a grid > but then allows the users to add as many columns and values as they > want > > hmmm, now that you have got me explaining this i'm thinking that this > may not be the right way to do it. maybe the better way to do it would > be to actually create the lookuptable in the background then if they > add a new column, then simply add that column to the table, if they > add a new row then add a new row to the table. then i can get rid the > last 3 tables and just have a lookupname table which just stores the > names and then holds a reference point to the tables that are built to > store the data in the background > > i suppose what confuses me now is how we would model this in OO for > the cfc's. would we have: > > - a lookup bean, which holds information relating to the lookup - > lookup id, and the table it references etc... also have a services, > dao and gw cfcs for this bean (functions would be addLookup(), > deleteLookup(), etc...) > - then also have another bean called lookupcontents which models the > data and will also have a services, gw, and dao cfcs for this bean > (this will be used for manipulating the data in the lookup table; such > as addColumn(), addRow(), addData() etc...) > > i think this sounds right, would really appreciate the feedback though > as i am fairly new to OO :-) > > thanks also for making me see that the 4 table model didn't look the > best option > > richard > > On Sep 16, 11:59 pm, Baz <[EMAIL PROTECTED]> wrote: > > Hey Richard, a couple of questions for clarification: > > > > - Is one of the goals of your system to model anything? For example, > > today it is GPs but tommorow it may be sports-cars, then laptops and > so on? > > Or maybe a little more narrow but similar in concept, nurses, interns, > > surgeons, etc? If so, would you store that primary id in the "lookup" > table? > > - Is the "lookupValues" table where you store *possible* values for a > > given field and lookupData where you store the actual value? For > example a > > column of "title" would have lookupValues of "Mr.,Dr.,Mrs.etc." and a > > lookupData of just "Dr."? > > > > Cheers, > > Baz > > > > On Mon, Sep 15, 2008 at 7:01 AM, Richard (J7) <[EMAIL PROTECTED]> > wrote: > > > hi, > > > > > we have a feature in out software that allows users to create multi > column > > > lookups: > > > > > e.g. they can create a lookup for gp which would look like the > following: > > > > > value | gp name | go title | gp address 1 | etc.... > > > > > --------------------------------------------------------- > > > > > 0 Smith Dr 25 Gate Road ..... > > > > > 1 May Dr 12 Old Street ...... > > > > > the user can add any columns they want, and then add as many values as > they > > > want, and start adding gp details in the grid in the database we have 4 > > > tables to store this data: > > > > > table 1) lookup (which stores just the name of the lookup - in this > case > > > gp) > > > > > table 2) lookupValues (which store the values - 0,1,2 etc....) > > > > > table 3) lookupColumns (which store the columns - 'GP Name', 'GP > Title', > > > 'GP Address 1', etc...) > > > > > table 4) lookupData (which holds the actual data and references to the > > > value and columns that it is data for - e.g. it holds lookupName=GP, > > > lookupValue=1, lookupColumn=GP Name, lookupData=Smith) > > > > > we are getting stuck on knowing the correct way to model this in cfc's > and > > > OO. we usually create a Bean for the object, a service, a gateway, and > a > > > dao. but where do we start for this? do we have a single lookup bean > and > > > then a gateway and dao for each table, or do we simply have one bean, > > > service, gateway, dao. if we have one bean then how are the properties > of > > > that bean going to be built up, and how are the getters and setters > going to > > > reflect this grid structure of this lookup object. really appreciate > any > > > help, > > > > > thanks > > > > > richard > > > > > Regards, > > > > > Richard White BSc (Hons), MBCS > > > > > Director > > > > > J7IS Ltd > > > > > Web Application Development > > > > > *Contact Details:* > > > > > Email 1: [EMAIL PROTECTED] > > > > > Email 2: [EMAIL PROTECTED] > > > > > Web:www.j7is.co.uk > > > > > Tel: +44 (0)798 472 1810 > > > > > *Head Office (Postal Address):* > > > > > J7 Group > > > > > University College London > > > > > Department of Computer Science > > > > > Gower Street > > > > > London > > > > > WC1E 6BT > > > > > *Registered Details:* > > > > > *J7IS Ltd* > > > > > *3a Livingstone Court* > > > > > *55 Peel Road*** > > > > > *Harrow*** > > > > > *Middlesex* > > > > > *HA3 7QT* > > > > > * * > > > > > *Company Number: 05613376* > > > > > *VAT Number: 924 9392 93* > > > > > > --------------------------------------------------------------------------------- > > > > > This e-mail is confidential and privileged. If you are not the intended > > > recipient please accept our apologies; please do not disclose, copy or > > > distribute information in this e-mail or take any action in reliance on > its > > > contents: to do so is strictly prohibited and may be unlawful. Please > inform > > > us that this message has gone astray before deleting it. Thank you for > your > > > co-operation. > > > > > * * > > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "CFCDev" 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/cfcdev?hl=en -~----------~----~----~----~------~----~------~--~---
