Jim Lawrence
accessd at shaw.ca
Tue May 6 17:18:40 CDT 2014
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