Darryl Collins
darryl at whittleconsulting.com.au
Tue May 6 18:47:35 CDT 2014
Wow... Firstly thanks to the Jims, Bill and Arthur for a great read this morning (Tiz Wednesday AM in Oz) - That was very interesting. Arthur, I will follow up your talks later today - thanks for letting us know they are there. Bill: Have to agree with the "Effective Date" and "End Date" approach in many business situations is the best and only way to go. All 'Big Business' databases I have worked on for the past decade or more have used this approach. There generally is no performance issues as any 'working data' is called off a subset of the main data (Say a View in SQL Server terms) where the query is restricted (unless otherwise specified) to data that is valid in the requested date band. This is particularly important with tasks where you move around in time. One example is a tax rate or surcharge. These change all the time and are only effective from Date X to Y and then the change again. If you are going back in time (say to do a refund or adjustment) you need to be able to apply the correct rate for the date ranges selected. If a policy cover a whole year and the rate changes in that year, you need to be able to apply the correct pro-rata rate for the date range. There is no way in hell you are going to do something like "UPDATE GST_RATE to .15" globally and have it work. You need to have a table live (Using GST_RATE as the example here) StartDate | EndDate | Rate 1-Jan-2001 31-Dec-2001 0.05 1-Jan-2002 31-Jul-2004 0.07 1-Aug-2004 31-Dec-2006 0.85 1-Jan-2007 31-Jul-2007 0.67 Etc... Cheers Darryl -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson Sent: Wednesday, 7 May 2014 8:38 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Should Business use Access? Well if you are backing up I wouldn't think you would worry too much about deleting tables. Deleting rows is not just about storage, it is also about performance. If you don't need the data, jettison it... and save the "big storage is cheap" rallying cry for your backup data. On May 6, 2014 6:19 PM, "Jim Lawrence" <accessd at shaw.ca> wrote: > You are right, that seems to be the latest concepts of databases. > > No records are deleted only added...they can be flagged as archived or > timestamped updates. Big online, databases do not have the time to do > real deletes or updates. For years we were always been fighting for > storage space but now we can afford to backup, backup and backup. > > Jim > > ----- Original Message ----- > From: "Arthur Fuller" <fuller.artful at gmail.com> > To: "Access Developers discussion and problem solving" < > accessd at databaseadvisors.com> > Sent: Tuesday, May 6, 2014 2:41:29 PM > Subject: Re: [AccessD] Should Business use Access? > > Precisely my point, Jim. This is not about a weightlifting contest, I > had no intention of this thread going there, but I did mean to > distinguish between an app that takes a month or two to write and > another that takes a year or two to write. In large part, it concerns > the number of tables, stored procedures and queries. Fair enough, but > it also concerns the number of simultaneous users and their > geographical distribution. That latter point can be huge if the firm > has 40 offices in 10 countries. That introduces significant > complexites > > And none of this addresses the PITA issue (Point In Time > Architecture), which basically says that the traditional relational > concepts of Insert and Update and Delete are destructive of what data > existed a moment ago. That is a very significant problem and requires > a whole lot of planning, but to oversimplify, it means that Updates > and Deletes are forbidden, and that every table in the DB be able to > handle this. That means that every table has a pair of date fields > (call them EffectiveDate and EndDate), and that all queries respect > these fields. For example, take your average 50-year-old medical > patient: she has probably had a few family physicians, optometrists > and so on, due to moving to Seattle for a new job and a year or two later it's Denver. > > What this sort of database requires is the ability to wind the clock > back to 2002, say. That is flatly impossible with the traditional > Insert, Update, Delete model. The only way this can be handled is the PITA model: > every table has EffectiveDate and EndDate columns and every query and > stored procedure respects these columns. Who was your dentist in 2004? > Who sold you that Ford that you gave to your daughter in 2012? Where > did you live in 1999 and who was your physician at that time? > > These questions describe the fundamental problem with traditional RDMS > -- every action is destructive to what existed a moment ago. I have > written about this problem and described a solution or two in Simple-Talk.com. > Maybe I didn't cover every eventuality but I tried to cover most. > Anyone interested in these problems is invited to search > simple-talk.com and see my initial research. I make no claim to > definitive research; this is a topic for lots of contributors. I need help with taking this further. > > Arthur > > > On Tue, May 6, 2014 at 3:31 PM, Jim Lawrence <accessd at shaw.ca> wrote: > > > > > A few years ago, when working on one government project, (it was > > Oracle), the number of tables was such, that the layout of the > > schema covered two walls in my office. I honestly have no idea how > > many tables and queries were involved, today, but I remember that > > the print out was done so we could keep track of what was happening > > as no one on the project could > just > > remember everything off the top. > > > > Jim > > > > ----- Original Message ----- > > From: "Arthur Fuller" <fuller.artful at gmail.com> > > To: "Access Developers discussion and problem solving" < > > accessd at databaseadvisors.com> > > Sent: Tuesday, May 6, 2014 8:42:48 AM > > Subject: Re: [AccessD] Should Business use Access? > > > > Jim Hewson (not Lawrence), > > An Access app consisting of 72 tables is not a complex app. First of > > all, we need two better words than "complex". I suggest "Rich" and > > "Large", whose terms I think I inherited from Chris Date, but it > > might have been Fabian Pascal, or even the Father of Us All, Codd > > (in close company I > call > > him The Coddfather). > > > > History aside, I would describe a complex app as consisting of at > > least > 500 > > tables, and there goes one of the main reasons why Access int its > packaged > > version falls a tad short of the mark. In specific terms, the > Relationships > > tool is woefully inadequate even with 55 tables, let alone 500. > > That's > when > > you need SSMS or equivalent tools -- when you need 10 relationship > diagrams > > not one, and when you need to concentrate on one aspect of the app > > rather than the whole app, and that one aspect might involve 100 > > tables. This is especially important when the team involves several > > programmers, and also when developers die or retire, and new hires > > have to be brought up to speed. > > > > I neglected to define the terms Rich and Large. Rich means there are > > hundreds of tables, many of which have a relatively few rows. Large > > means they are relatively few tables, but they contain millions of > > rows. It is also possible that a given database is both Rich and > > Large, but that happens rarely. Usually the system is one or the > > other. I've developed a couple of exceptions, for example the > > software that runs a nuclear plant; that is way different than an > > app for a Mom'n'Pop hardware store or a household renovation firm. > > Suppose for example that you need 584 parts to assemble Product XYZ, > > and that every single part is potentially available from several > > vendors, and that you need to check who can supply 486 of these > > parts by tomorrow. You need to interrogate their inventory, and > > suppose that nobody has 486 parts, so you have to split the order > > and ensure that the deliveries shall arrive in time to fill your > > orders. This sort of problem is not trivial. Failure could conceivably cost $750k per hour. > > > > I've done a lot of apps like this. It's expensive, but the > > development > time > > is peanuts compared to the profit if it works. Such companies > > understand this, and so do we developers. It has got to work, or > > millions could be lost in a day of down-time. Working on such > > projects has cost me a couple of marriages, because I was two days late for dinner. > > > > I'm too old to this stuff any more, but I remain fully aware of how > > difficult it is. There is definitely truth to the maxim that your > thinking > > slows as you age, and it is also true that a young buck just out of > > university hasn't the faintest glimpse of the complexity of such > problems, > > or the costs of failure. In jobs like this, failure is not an option. > > > > I have done this sort of app, using Access with a serious server > > using > SQL > > Server and also MySQL. 500 tables and dozens of forms and 2k stored > > procedures and queries. This stuff is non-trivial. It demands a > > whole lot of design time and a whole of development time. You do it > > one piece at a time, starting with the most financially critical. > > The whole project > takes > > several years, which enables you to buy a nicer car and perhaps a > > new couch. You work insane hours and eventually your spouse leaves you. > That's > > the truth. > > > > A. > > > > -- > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > -- > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > > > -- > Arthur > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com