Vic JELICL LU ILIdNILIH d Udldd5e€ JESISA GOADIC IS tO keep it simple, and this implies modularity. e The Two-Headed Arrow—many-to- many relationships 2 The Headless Arrow—one-to-one re- lationships » Optional Arrows—splitting entities e Hard Sets, Soft Sets, and Summary Fields —integrity vs. ease of use The Identifier and Its Name—the most- used design pattern e The Past Event—when audit trail is mandatory » The Future Event—productive skep- ticism The Template—cookie-cutter records % The Self-Related Record—bill-of-materi- al structure " Numbers, Keyfields, and Real Things—a completion checklist The hallmarks of a sound database design are that it is durable and doable. A durable design survives changes in the busi- ness environment. Managers, policies, products, and styles all come and go. But a database represents millions of dollars' worth of painstakingly collected data about our firm and we'd rather it didn't come and go with them. A doable design is one that's easy to implement. It capitalizes on the one gift we all share (that we get better at any- thing each time we repeat it) and doesn't penalize our common flaw (we never get something right the first time). Durable databases can survive the very applications that created them. I recall a shop that has been through two payroll systems, a now-defunct workmen's comp system, and two security systems in the past six years. Yet their employee database, [ Voo - D00 SEME; 84 DATAMATION which those applications used, has been chugging away uninterrupted since it was first installed. At the other extreme lies the painful case of a design so dependent on transitory management style that it was unknowingly wrecked by the stroke of an executive pen mere weeks after implemen- tation. Understand, durability is no acci- dent. We deliberately build it into designs by modeling underlying business reality and by making sure the result is shared among applications and can be expanded with new data. REFLECT Modeling reality means REAL that our data structures EVENTS reflect real events, real activities. They must not simply mimic records in other filing sys- tems such as forms and documents. Forms can disappear without a trace, while prod- ucts, vendors, customers, and employees cannot. We model reality by letting our de- signs be determined by the business pro- cesses they will support. We call this process-driven data design, and we will re- turn to how it is done in a moment. Sharing it among applications means that different people use the same database for different purposes. Avoiding redundancy, DBAs call this, and there are three reasons why it's important. First, re- dundant data implies redundant data upda- tors, and it's pointless to have two people doing the same job. Also, the more users, the greater the incentive to keep it accurate, which means the data are more timely and reliable for everyone. Finally, the more MEANWHILE , ACROSS TOWN... V lab widely a database is shared, the more dura ble it is. Its users hold it steady despite cu yearly reorganizational tempests. | Making it expandable so new dataj can be added means developing the skills and tools to add new fields to existing rec ords, new records to existing files, or ne files to the database without shutting down Or retrofitting existing applications. Ex. pandable databases are more durable be. cause each new application brings new data needs. Application developers will use the central database if their needs are easily ac- commodated. Otherwise, each group could go its own way, and the centrally shared data pool would be stillborn. The most doable database designs are those that can be brought up faster andi with less effort than comparable flat files. A. The worst are disasters where the applica- tion guickly reaches 90% complete and stays there for months. There are two se- Crets to making it doable: include only what we need, and keep it simple. Data design conceals a treacherous twist to the dilemma: do we want it right or do we want it Friday? It's the urge to in- clude too much. Designing a vendor file for a payables system, we conclude that we need vendor ID number, name, address, and amount owed. We should stop, but temptation draws us on. We're designing a database to be shared by future applica- tions, we reason. Shouldn't we find out what they'il need and include it too? The bait looks tempting—doing a thorough job— and the risk looks slight—a few days' extra work. But, to do it, we'd have to iden- tify every data element and every vendor attribute that any user could ever want for any conceivable purpose. In short, it be- comes an endless undertaking. The power of database management systems is that they enable records to be stretched to include new fields as new ap- plications arise, and do so without making us track down and recompile existing pro-j grams. Conseguently, the first way w make a database design doable is to inclu only what we need at the time we design iL The second secret of doability is keep it simple, and this implies modularity. There are really only a handful of basic database design patterns. We use them like' building blocks, in one application after an other. We build designs out of them rather than deriving each application from scratch. We'il introduce these patterns ia ' the ninth instaliment. We'll start, though. next time with dataflow dynamics. 9% Frank Sweet is corporate manager of data administration for the Charter Co.., Jacksonville, Fla.