Shamil Salakhetdinov
shamil at users.mns.ru
Fri May 4 16:37:14 CDT 2007
Hi All, I'm aware that the subject of this message could be a new and potentially "flame provoking" therefore I have to tell you in the very beginning that my only intention is to find what are weak and what are strong positions of proposed below Lazy/Agile database development approach... Here it's - an agile (?!) non-traditional(?!!) modern(?!!!) approach to the original subject question: "Re:When to Use a Junction Table" - do not care what will happen tomorrow - use simplest possible solution and make your customer happy today ASAP. When tomorrow comes and your customer will change their minds you'll get your customer promptly happy again because you know some "secrets" of modern agile development: for database-centered development and MS SQL one of these "secrets" are INSTEAD OF TRIGGERS: - imagine your customer wanted to keep information about his Customers, Products and Quantities of Products used/ordered by Customers; - classical/traditional solution is to create (at least) three tables: tblCustomer, tblProduct and tblProductsOfCustomer; - but you're agile developer and you create just one table tblProductsOfCustomer (yes, with natural compound (CustomerName, ProductName) primary key!) and one view - ProductsOfCustomer on tblProductsOfCustomer table. That's it for "database design" - I quoted "database design" because a professional traditional database designer/developer can hardly call what I have just done as database design - he will call that "design" something like "1-st grade botanic student garbage collection table"... Then you create a form (MS Access form using linked view or VB2005 form with Gridview) on ProductsOfCustomer view to enter customer and products information - and you're done in 15 minutes. Your customer is happy. He pays you 100 bucks for this your agile 15 minutes work: another consultant planned to charge your customer 300 bucks because they planned to create three tables, with identity primary keys and "all that jazz" - so your customer is even double happy - he saved 200 bucks. And he is ready now to use your solution in his real life business and this promptly made solution will save your customer even more money... But then tomorrow comes and your customer realizes that he sometimes enters different names for the same customer or the same product by thus getting "dirty" data. Usual story isn't it? Yes, it is, but by the moment your customer realizes he has some problems with your solution he has already saved quite some bucks because of the first version of the system you developed for him so quickly - therefore he is ready to pay you some more bucks for your agile development... And here you're - you tell him, OK, no problem but this solution will cost you this time 300 bucks because I need to do more work than previous time. And your customer doesn't argue - as I told you he saved already quite some bucks because of the usage of your system in his real life business and another developers planned to charged him this time 500 bucks to "fix your work and to implement the new flexible advanced solution"... And you do the "trick": 1. create this time three tables with identity keys (5 minutes); 2. modify ProductsOfCustomerView using joins (5 minutes); 3. write instead of insert, update and delete triggers - couple of hours if you do that first time and 15 minutes (5 minutes for each trigger) if you do that kind of work on regular basis; 4. modify GUI design - create a form to enter customer information, a form to enter product information and modify the form to enter product quantity information to use combo-boxes (or something like that)... And you're done in approx. 1 hour if you're experienced agile developer and in 3 hours if you're a novice one but know agile "secrets" and "tricks"... Your customer is happy again. You are happy too - you outperformed your competitor and you have got in total 400 bucks where your competitor planned originally to do all the work for 300 bucks. And what is important - from financial point of view the fact that you was paid 100 bucks more doesn't influence/impact badly your customer's business somehow because he got your first solution very promptly and used it in his business and got his profit (instead of waiting super duper costly original solution of your competitor)... Of course I do (over-)simplify here the possible real life development scenario but I do think that using INSTEAD OF TRIGGERS in MS SQL lets developers to become "lazy" or "agile" - how you call them depends on your point of view... Interesting to hear now your opinions, dear all, on this optimistic agile development practices I described above - what potential bottlenecks do you see with that non-traditional(?) agile, flexible, effective and always(?) more efficient and more profitable for all involved parties approach comparing it with going traditional way your competitors' development approach?... Thank you. -- Shamil