Thursday, January 30, 2014

In The Data Trenches - Part 3 - Table Design


A database table resembles a spreadsheet – data arranged in rows and columns.  However, a spreadsheet is intended to process integers, to sort numbers and perform complex calculations.  In contrast, a database program is designed to organize more disparate types of information, such as dates and text.  The data types available in MS Access 2013 are:

Short Text

Number

Currency

Auto Number

Date and Time

Yes/No

OLE Object

Lookup and Relationship

Rich Text

Long Text

Attachment

Hyperlink

Calculated Field

(As previously mentioned, Access is used as our exemplar because of its wide availability.)

Each field in a table is assigned one of these types, and all information entered into that field must of that type.  For example, entering a hyperlink into a Currency field would produce an error message.

We can apply these fields to the “Status” table discussed in our earlier posting on normalization:

Field Name                                         Field Type

Project                                              Lookup and Relationship

Current Status                                 Long Text

Status Date                                      Date and Time

Next Steps                                       Long Text

Waiting For                                      Long Text

Notes                                                Long Text

The Long Text fields would present fill-in-the blank cells, while the Date field presents a calendar dialog box.  Lookup and Relationship allows the database designer to create either a drop down list of standard terms to be used in this field (“Lookup”) or to tie this table to another one in the database (“Relationship”).  In our example, this table would be tied (“related”) to the main “Projects” table, and the names of all projects would appear here as a drop down list.  The user would merely need to click “Project 1,” “Project 101,” or any other name on the list.  A look up list helps ensure accurate and uniform data entry.  If “Agmt” is the specified description, users must use it, and may not substitute their own terms, such as “contract” or “agreement.”  While these may be accurate, they complicate attempts to search the database. 

While it is helpful to describe each data type consistently and uniformly, each project needs a unique name or label.   Presume the database is intended to organize a company’s contract files.  Individual documents might be described as “agmt,” “corr” or “draft” without causing confusion.  But simply labeling each supply agreement as “supply agmt” could result in confusion.  Which agreement corresponds to which vendor?  A descriptive, and unique name is called for, such as “supply agreement – ABC Co – 2014”. 

 

Which brings us to a fault in the table described above.  It does not ensure that each entry will contain at least one unique entry.  The standard solution is to designate a “key” field, which must be unique, meaning that no two records in the entire database will be identical.  To ensure that the “key” is truly unique, one might specify that it will be an “Auto Number” field, directing the database program to automatically number each record as it is created.  The updated table design might appear as follows:

 

Field Name                                         Field Type

ID                                                         AutoNumber

Project                                                Lookup and Relationship

Current Status                                   Long Text

Status Date                                        Date and Time

Next Steps                                         Long Text

Waiting For                                         Long Text

Notes                                                    Long Text
Once the design is finalized – and the requisite relationships created - one may begin to enter information directly into the table, much as one would with a spreadsheet.  Or one may elect to create an entry form to speed up the process. 

Friday, January 17, 2014

In The Data Trenches, Part 2 – Normalization

Spreadsheets store information in what is known as a “flat file” format – a chart X columns wide and Y rows long.  In contrast, relational databases store information in “tables,” which are connected via “links” or “relationships.”  These relationships allows smaller tables to be combined to provide all the information found in a larger file, but in a format that is easier to administer.
Consider this list of fields in a spreadsheet used as an address book:

Company Name
Address1
Address2
City
State
Zip
Contact Person
Contact Title
Contact Phone
Contact Cell
Contact eMail
 
This file is simple to build and simple to use.  It can be quickly tied to a mailing program to automate correspondence with the contact person.  It is also relatively easy to change if the contact has a change of title or phone number, or is replaced as the principal contact.

But this file does not provide an easy way to track a contact to any new assignment or employer.  One might create a “Contacts” tab in a workbook, and keep individual information there.  But that quickly creates the need to manually cross reference between the “Client Company” tab and the “Contacts” tab, which is inefficient and opens the door to errors.  A relational database can perform such cross references automatically, by tying two different sets of data together.  In this case, the two data sets might be laid out as follows:
Company Name                                       Contact Name
Address1                                                 Company Name
Address2                                                 Title
City                                                         Work Phone
State                                                       Mobile Phone
Zip                                                          eMail

The cross reference is the duplicate field “Company Name.”  Information entered into the first table automatically becomes available in the second, typically as a drop-down selection.  That is one of the great advantages of a relational database – when well designed, it requires information to be entered only once, but permits it to be used in many ways.  But this strength rests on one of greatest complications of relational databases – the need to “normalize” the tables or data sets.

“Normalization” is the process of arranging tables and fields to avoid duplication.  In practice, it is the process of breaking a large data set into component parts, and tying those parts together with relevant links or relationships.

Consider some relevant questions about a client:

·         What is their contact information?

·         How many projects are we handling for them?

·         Who is the contact for each project?

·         What is the budget for each project?

·         What are expenditures to date for each project?

·         What is the current status of each project?

·         What are the next steps for each project?

·         What obstacles have been encountered?

Creating one flat file to capture these disparate types of information would tax even a spreadsheet expert.  A relational database, however, permits each of these questions to be addressed in turn, using tables for Client, Contacts, Projects, Expenses and Status.

CLIENTS                               CONTACTS

Company Name                     Contact Name
Address1                               Company Name
Address 2                              Title
City                                       Phone
State                                     Mobile
Zip                                        eMail
PROJECTS                             EXPENSES                      

Project                                   Project                            
Company Name                      Expense Amt                   
Start Date                              Expense Type                   
Est Completion Date                Date                               
Contact Name                         Notes                               
Budget                                                                          
Notes

STATUS

Project
Current Status
Status Date
Next Steps
Waiting For
Notes

The tables Clients, Projects, Expenses and Status are in order.  Information flows from Clients to Projects via the Company Name field, while Projects, Expenses and Status are all tied using the Project field.  The information for Company Name and Project need be entered only once, suggesting that this portion of the database has been normalized.

The Contacts table, however, may be problematic.  Note that it shares information with both Clients and Projects.  While these relationships do not conflict, they will require that care be taken to ensure that the correct data sources are used when reports are generated or queries run.  A better approach might be to delete the Contact Name field from Projects, and create a separate table consisting of only Contact Name and Project. 

We will explore this question in our next installment, on table design.

Thursday, January 9, 2014

In The Data Trenches, Part One: The Limitations of Spreadsheets

The spreadsheet is central tool of business.  It allows users to easily, rapidly and accurately perform complex analyzes and calculations, providing insights vital to commercial success.  It is also a popular tool for inventories, contact lists, and even complex catalogs of witnesses and testimony or contracts and their terms.  As these catalogs grow, they become unwieldy, particularly if one wishes to cross reference between sets of data.  Spreadsheets are not designed to address such “relationships;” but relational databases are.  This series will discuss the fundamentals of databases and build a simple project tracking tool to illustrate the many benefits these programs can offer.

While there are a number of relational database programs on the market, Microsoft Access is probably the most widely known.  These articles will therefore use Access as their exemplar, but that is not intended as an endorsement of Access over any of its competitors.  Similarly, spreadsheet examples will be based on Microsoft Excel.
A correspondence file is a simple example of a spreadsheet used for record keeping might include the following fields:
  • Date
  • To
  • From
  • Regarding
  • Summary
  • Next Steps
 Technically known as a “flat file,” this file is simple to build and simple to use.  The column headings can be added in moments, and the list can be continued virtually forever, with new rows of entries added as needed.  But that simplicity belies the limitations inherent in this tool. 

A sample entry might be:

  • Date:              1/1/14
  • To:                  Ms. S. Smith
  • From:             Atty. Jones
  • Re:                  Black Acre
  • Summary:     Notice of alleged ground water contamination
  • Next Steps:  Organize meeting with regulators 

But imagine a file containing thousands of such records, on thousands of subjects.  Then imagine scouring the file for all correspondence regarding “Black Acre.” Experience spreadsheet users will recognize that the search can be completed with a simple “Find” (or “Sort") command; the less experienced might well become lost.  In addition, data needs to be keyed into each and every cell (even if one relies upon the "auto complete" features found in current spreadsheet programs).  “Ms. Smith” and “Black Acre” need to typed each time an item of correspondence is recorded.  Each  entry introduces opportunity for error.  “Black Acre” might become “Blak Arce,” “Blac Acre,” “Blk Acre,” or any other combination that tangled fingers might produce.  An electronic search for “Black Acre” will not produce records logged under “Blk Acre.” 

But there are other weaknesses:

  • This file does not offer any contact information.  One must look elsewhere for Ms. Smith’s address. 
  • Shipping fees are evidently captured elsewhere.
  • Each “Next Step” must be added manually to yet another separate record or tracking tool. 
To fully document that January 14 letter to Ms. Smith regarding Black Acre, one must consult this file, the firm’s address book, the billing system and the document management system, if one wants to see the letter itself.  Four searches for one document is hardly efficient.  More, those searches would not touch the underlying document - the notice of contamination mentioned in the "Summmary" field.

A relational database would provide a more robust tool, although it would require more set up.  It would contain separate files (to use the correct term, "tables") for contact information, projects, expenses, next steps and correspondence, tied together through “relationships,” and organized in such a way that each item of information only need be entered once.  After that, each item may be used again and again via drop-down menus.

Organizing these tables to ensure one time data entry is one of the keys to a successful database.  We will take up that question in our next installment.

 

 

Tuesday, October 8, 2013

"R" is for Remedy

The traditional definition of a contract is “offer, acceptance and consideration.”  I offer Seller $100 for a car.  Seller accepts; I hand over the money.  Seller is obliged to turn over the keys.

But what if Seller does not?
What if Seller hands over the keys to a vintage Yugo, rather than the ’57 Chevy I saw in the drive?
If I discover the car lacks an engine, an interior, tires, or I don’t like the color?
I need a remedy.
The contract outlined does not contain any remedies, let alone any warranties.  As a result, my only recourse is to the standard remedies provide by contract law:
Money damages. 
The “benefit of the bargain,” or the money I would have made had the deal gone through.  In this case, I am out the $100 I paid to Seller, but also planned to sell the car for a $500 profit.  Arguing whether Seller is liable for that $500 would enrich the lawyers for both sides.
Restitution.
Full return of the money or property given as consideration.  Regarding the car, Seller might simply offer to give me my money back.
Rescission.
The contract is set aside as the result of fraud, mistake, duress or undue influence.  Rescission would not be available in the car transaction, as described.  Money or other property given as consideration is returned.
Reformation.
The court rewrites the contract to correct errors or inequities.  Courts are reluctant to grant reformation, believing it is the duty of the parties to their homework before executing an agreement.  More, the parties are always free to rewrite their contract privately, without recourse to the courts.
Specific Performance.
The court orders one party to hand over the goods or perform the services specified in the contract.  Available only if money damages are inadequate – for example if the goods or services are not available from any other source.  Generally not available, however, in contracts for personal services, as courts regard compelling someone to perform work against his or her will to be too close to slavery.  Beyond that, who would want vital services performed by someone not committed to doing their best?
These remedies are often insufficient, at least in the context of an IT transaction.  Remedy is available only after the fact, generally after much litigation and is typically limited to money damages.  The advantages expected from a new system, such as reduced costs, increased productivity or competitive advantage, generally cannot be recovered, not to mention the value of the time employees might have spent trying to correct a faulty system.  To compound the loss, the costs of litigation generally cannot be recovered.

The statutory remedies, then, do little to address the needs of the aggrieved IT buyer, who needs a system that works, on time, and for the agreed price.  Thus the best remedy for a faulty system, or a simple disagreement with the vendor, is diligence in identifying the business need the new system will address, in designing the system and in setting an implementation plan designed to identify and solve problembs before the system enters productive use. 

Wednesday, September 18, 2013

Open Source - Reciprocal Licenses


THE FINE PRINT

Open Source – Reciprocal Licenses

Open source software is now a fact of life. The Apache HTTP Server software is an integral part of the Web, while Unix and its progeny are common Enterprise tools. Individual users may surf the Web via Mozilla Firefox, replace Microsoft Office with Open Office or Libre Office, and Ubuntu offers an alternative to both Microsoft Windows and the Apple OS. More, open source “software foundries” have proliferated, offering new programs and programming tools at little or no cost. Download, install and use.

If only it were that easy.

Open source” code is distributed under a license that permits users to freely access and alter the underlying source code; licenses for proprietary software, in contrast, routinely forbid such efforts. In addition, open source products are offered free of charge. It is an attractive proposition – good software, readily available (from the Internet) at little or no cost. However, while open source code may be “free,” in the sense that no licensing fee is required, but it is not “free” of legal restrictions. A variety of “open source licenses” have been developed by different members of the open source community. Open source developers are generally free to chose which license will apply when they make their products available to the public. Moreover, a program constructed from a variety of open source products could be subject to a number of different open source licenses. The license analysis can become rather complex.

One feature of open source licenses that causes concern is the “reciprocity” requirement, which provides that when open source code is combined with proprietary code, the resulting product must be released as open source. Such a requirement would be unacceptable to users who make their money licensing proprietary software.

But this description is overly broad. Only some open source licenses contain a reciprocity requirement; some do not. Perhaps the best known example is the GNU General Public License (“GPL”), version 2 of which provides:

2. You may modify your copy or copies of the Program or any portion of it, thus forming a work based on the Program, and copy and distribute such modifications or work under the terms of Section 1 above, provided that you also meet all of these conditions:

b) You must cause any work that you distribute or publish, that in whole or in part contains or is derived from the Program or any part thereof, to be licensed as a whole at no charge to all third parties under the terms of this License.

Two things should be noted regarding this “reciprocity” provision:

  • It applies to works that one distributes or publishes, not works that one creates for private (in-house) use.
  • It permits one to publish proprietary products “along side” open source:
To do this validly, you must make sure that the free and non-free programs communicate at arms length, that they are not combined in a way that would make them effectively a single program.
The difference between this and “incorporating” the GPL-covered software is partly a matter of substance and partly form. The substantive part is this: if the two programs are combined so that they become effectively two parts of one program, then you can't treat them as two separate programs. So the GPL has to cover the whole thing.
If the two programs remain well separated, like the compiler and the kernel, or like an editor and a shell, then you can treat them as two separate programs—but you have to do it properly. The issue is simply one of form: how you describe what you are doing. Why do we care about this? Because we want to make sure the users clearly understand the free status of the GPL-covered software in the collection.
------- http://www.gnu.org/licenses/gpl-faq.html#WhatDoesCompatMean (September 18, 2013).
A simple question remains: Does the benefit provided by a program governed by a reciprocal license exceed the costs of releasing the resulting product without charge and with no protection for the source code?


Per GNU, the current reciprocal open source licenses are:

  • GNU General Public License (GPL) version 3 (#GNUGPL) (#GNUGPLv3)
  • GNU General Public License (GPL) version 2 (#GPLv2)
  • GNU Lesser General Public License (LGPL) version 3 (#LGPL) (#LGPLv3)
  • GNU Lesser General Public License (LGPL) version 2.1 (#LGPLv2.1)
  • GNU Affero General Public License (AGPL) version 3 (#AGPL) (#AGPLv3.0)
  • GNU All-Permissive License (#GNUAllPermissive)
  • Apache License, Version 2.0 (#apache2)
  • Artistic License 2.0 (#ArtisticLicense2)
  • Clarified Artistic License
  • Berkeley Database License (a.k.a. the Sleepycat Software Product License) (#BerkeleyDB)
  • Boost Software License (#boost)
  • Modified BSD license (#ModifiedBSD)
  • CC0 (#CC0)
  • CeCILL version 2 (#CeCILL)
  • The Clear BSD License (#clearbsd)
  • Cryptix General License (#CryptixGeneralLicense)
  • License of the ec fonts for LaTeX (#ecfonts)
  • eCos license version 2.0 (#eCos2.0)
  • Educational Community License 2.0 (#ECL2.0)
  • Eiffel Forum License, version 2 (#Eiffel)
  • EU DataGrid Software License (#EUDataGrid)
  • Expat License (#Expat)
  • FreeBSD license (#FreeBSD)
  • Freetype Project License (#freetype)
  • License of the iMatix Standard Function Library (#iMatix)
  • Independent JPEG Group License (#ijg)
  • License of imlib2 (#imlib)
  • Intel Open Source License (#intel)
  • ISC License (#ISC)
  • Mozilla Public License (MPL) version 2.0 (#MPL-2.0)
  • NCSA/University of Illinois Open Source License (#NCSA)
  • OpenLDAP License, Version 2.7 (#newOpenLDAP)
  • License of Python 2.0.1, 2.1.1, and newer versions (#Python)
  • License of Python 1.6a2 and earlier versions (#Python1.6a2)
  • License of Ruby (#Ruby)
  • SGI Free Software License B, version 2.0 (#SGIFreeB)
  • Standard ML of New Jersey Copyright License (#StandardMLofNJ)
  • Unicode, Inc. License Agreement for Data Files and Software (#Unicode)
  • The Unlicense (#Unlicense)
  • License of Vim, Version 6.1 or later (#Vim)
  • W3C Software Notice and License (#W3C)
  • License of WebM (#WebM)
  • WTFPL, Version 2 (#WTFPL)
  • X11 License (#X11License)
  • XFree86 1.1 License (#XFree861.1License)
  • License of ZLib (#ZLib)
  • Zope Public License, versions 2.0 and 2.1 (#Zope2.0)
  • WxWidgets License (#Wx)
------ http://www.gnu.org/licenses/license-list.html#GPLCompatibleLicenses (September 18, 2013).
Further Reading:

For a more detailed discussion of the definition of “open source,” see the Web site of the Open Source Initiative at: www.opensource.org.

Open source software presents issues other than reciprocal licenses. For a discussion of all those issues, see:
Intellectual Property Deskbook for the Business Lawyer, Third Edition; Intellectual Property Committee of the American Bar Association, 2013 (Chapter 11).

For a detailed discussion of reciprocal licenses, see: http://www.gnu.org/licenses/license-list.html#GPLCompatibleLicenses



Wednesday, September 4, 2013

Is Your Confidential Information Adequately Protected?

Commercial nondisclosure agreements typically provide that confidential information will be kept in confidence, provided only to employees who have a need to know and who have been informed that the material is to be protected. In the event an employee breaches confidentiality, his/her employer is expected to pay for any damages that result.

It is less common to encounter agreements that require individual employees to be personally bound by the duty of confidentiality. The reason given is a valid one – individual employees generally do not have the means to pay for the damages that can result from unauthorized use or disclosure of confidential information.

This approach overlooks two exposures:

1. An employer has limited control over the actions of an employee. They may fire him or her, but that may not be a deterrent if the employee believes he/she has something valuable to sell.

2. Disclosure of certain information, such as personal financial records, requires immediate action.

Money damages collected months later may not begin to restore a damaged reputation, or the cost of regulatory proceedings.

Consider requiring employees who receive you confidential information to agree, individually, to keep your information in confidence and to grant you the right to seek immediate injunctive relief against them should they breach that confidence.

Monday, April 5, 2010

A Software Checklist


Documentation

Will you receive all the copies you need?
At what cost?
May you make and distribute copies for internal use?

Term and Termination

Are there any provisions that restrict your ability to simply stop using the software?
In what circumstances may vendor your right to use the software?
Do those circumstances include anything more than:
  • Your failure to pay undisputed balances when due?
  • You've pirated the software?
  • Vendor needs to replace the software to resolve an intellectual property infringement claim from a third party?

Breach

Does the contract provide for the termination of a material breach?
Does it define the term “material breach?”
If it doesn't, how will you know when you have the right to end the agreement and seek a refund?

Remedies
  • What happens if things go wrong?
  • What are your rights?
  • What are your obligations?
  • What are your options, short of litigation?

Fees

Is the license fee fully paid up or must you pay year and year out?
If the latter, what will you receive in return?
Are maintenance fees required or optional?
If required, why?
In either case, what will you receive in return?
What will happen if you elect not to pay?

Performance

Must you pay for maintenance releases, bug fixes and the like?
Are new releases and upgrades extra cost items? Why?
How do the parties determine when an extra charge is appropriate?
What are the performance standards for the product?
If you are disappointed, can you point to an objective standard that hasn't been met?

Warranties

Do they start when the product is ordered, shipped, delivered, installed or when you begin using it in production?
What remedies does the warranty provide and are you protected if vendor fails to respond promptly or fails to resolve the problem?

Payments

Are you permitted to withhold disputed payments?
May you hold back a substantial final payment until the software passes its final tests?

Acceptance Testing

Does the contract set out objective performance standards?
Mutually agreed testing procedures?
Timetables for correction, if the software does not pass the acceptance tests?

IP Indemnification

Is vendor required to respond promptly in the event a third party makes an allegation of infringement?
Does vendor have the resources – both technical and financial – to handle such a claim?

Escrow of Source Code

Everyone seems to want a source code escrow. Assume your contract provides one and a release event has occurred. Do you really have the wherewithal to put the source code to productive use?

Limitation of Liability

Imagine a worst-case scenario. How much harm could vendor cause you?
Does the limitation of liability match that exposure?

Insurance

Look again to that worst case scenario. Does vendor have the liquid assets to make you whole? If not, it would be sound business to require vendor to carry the appropriate levels of insurance, from reputable and financially sound carriers.