[Glastonbury] an idea

Kelvin McNulty glastonbury at mailman.lug.org.uk
Tue Aug 5 20:59:02 2003


--------------Boundary-00=_EID13KTT261LK9SZWL5S
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Dear Everyone,

I offer here an idea that might be useful - bookkeeping using LAMP.

I have attached details as developed so far, in RTF format, in a rather f=
ormal=20
format as this is the first time I have presented this concept in any kin=
d of=20
open space. I'd be happy to give a presentation on it in one of our meeti=
ngs=20
if anyone is interested.

Best,

Kelvin

On Thursday 31 July 2003 16:16, Mike Keogh wrote:
> Hi all
>
> After a stimulating conversation in the pub after the last Lugog meetin=
g,
> I promised to write up some of the ideas. So, here it is, a proposal fo=
r
> seeding a Linux "market place" in Glastonbury and London...

--------------Boundary-00=_EID13KTT261LK9SZWL5S
Content-Type: text/rtf;
  charset="iso-8859-1";
  name="lamp_bookkeeping.rtf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment; filename="lamp_bookkeeping.rtf"

{\rtf1\ansi\uc0 \deff1
{\fonttbl{\f1\froman\fcharset0\fprq2 Nimbus Sans l ;}{\f2\froman\fcharset0\fprq2 Bitstream Charter ;}}
{\colortbl;\red0\green0\blue0;\red0\green0\blue255;\red0\green255\blue255;\red0\green255\blue0;\red255\green0\blue255;\red255\green0\blue0;
\red255\green255\blue0;\red255\green255\blue255;\red0\green0\blue128;\red0\green128\blue128;\red0\green128\blue0;\red128\green0\blue128;\red128\green0\blue0;\red128\green128\blue0;\red128\green128\blue128;\red192\green192\blue192;}
{\info }\paperw11900\paperh16820\margl560\margr560\margt840\margb840\widowctrl\ftnbj\aenddoc\formshade \fet0\sectd
\linex0\endnhere\plain
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc
{\cf1{\fs36\f1 Representing Complex Spreadsheet Data Structures}}
\par
\pard \qc
{\cf1{\fs36\f1 using a Simple Database}}
\par
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc
{\cf1{\fs36\f1 Kelvin McNulty}}
\par
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc

\par
\pard \qc
{\cf1{\fs24\f1 This publication in this form Copyright (}}{\cf1{\fs24\f2 \u169 }}{\cf1{\fs24\f1 ) Kelvin McNulty 2003}}
\par
\pard \qc
{\cf1{\fs24\f1 The publication may be freely copied and used provided that an appropriate proportion of}}
\par
\pard \qc
{\cf1{\fs24\f1 any profits made are donated to the author.}}
\par
\pard \qc

\par
\pard \qc
{\cf1{\fs24\f1 Note: The information contained in this publication is not copyright and is published}}
\par
\pard \qc
{\cf1{\fs24\f1 here so as to be available for others to use.}}
\par
\pard \qc
{\cf1{\fs36\f1 Introduction}}
\par
\pard \qc

\par
\pard {\cf1{\fs24\f1 This document describes a method of representing a spreadsheet having many rows and columns in a database having three columns or fields of data in each record.}}
\par
\pard 
\par
\pard {\cf1{\fs24\f1 The method is very simple but not trivial. It is useful to represent and store a complex body of data using commonly available database software in place of having to use spreadsheet software.}}
\par
\pard 
\par
\pard {\cf1{\fs24\f1 I have chosen to formally write and publish this information so that the method is accessible to all to use.}}
\par
\pard 
\par
\pard \qc
{\cf1{\fs36\f1 Represting a Spreadsheet using a Database}}
\par
\pard \qc

\par
\pard {\cf1{\fs24\f1 Every occupied spreadsheet cell is represented in the database by a record which has these fields:}}
\par
\pard 
\par
\pard {\cf1{\fs24\f1 \tab Row: contains the spreadsheet row number of the cell}}
\par
\pard 
\par
\pard {\cf1{\fs24\f1 \tab Column: contains the spreadsheet column number of the cell}}
\par
\pard 
\par
\pard {\cf1{\fs24\f1 \tab Value: contains the data value held in the cell}}
\par
\pard 
\par
\pard {\cf1{\fs24\f1 Additional fields may be provided, if required, to simplify the administration of the database. Such fields could include:}}
\par
\pard 
\par
\pard {\cf1{\fs24\f1 \tab Record Number: a unique value}}
\par
\pard 
\par
\pard {\cf1{\fs24\f1 \tab Date/Time Stamp 1: value recording date and time the record was created}}
\par
\pard 
\par
\pard {\cf1{\fs24\f1 \tab Date/Time Stamp 2: value recording date and time record was last modified}}
\par
\pard 
\par
\pard {\cf1{\fs24\f1 To facilitate the transfer of data back to spreadsheet display, a further field may be provided thus:}}
\par
\pard 
\par
\pard {\cf1{\fs24\f1 \tab Format: the value here will specify how the data is to be displayed.}}
\par
\pard 
\par
\pard 
\par
\pard \qc
{\cf1{\fs36\f1 An Example of an Application}}
\par
\pard \qc

\par
\pard {\cf1{\fs24\f1 The three column database can be used to represent a double entry ledger for bookkeeping.}}
\par
\pard 
\par
\pard {\cf1{\fs24\f1 The row field contains a transaction number.}}
\par
\pard 
\par
\pard {\cf1{\fs24\f1 The column field contains a heading which is one of:}}
\par
\pard 
\par
\pard {\cf1{\fs24\f1 - a standard heading present in all transactions (such as date, details, reference, etc.)}}
\par
\pard 
\par
\pard {\cf1{\fs24\f1 - a standard heading present in many but not all transactions (such as name, comment, cheque number, job number, etc.)}}
\par
\pard 
\par
\pard {\cf1{\fs24\f1 - an account heading (which is one of at least two account headings used by the transaction) present only in those transactions having values in the specified account.}}
\par
\pard 
\par
\pard {\cf1{\fs24\f1 Accessing the data works like this:}}
\par
\pard 
\par
\pard {\cf1{\fs24\f1 - A complete transaction is fetched by retrieving all records having the same row value.}}
\par
\pard 
\par
\pard {\cf1{\fs24\f1 - A complete account (list of all values entered under an account category) is fetched by retrieving all records having the same column value. The row values can then be used to fetch any required transaction data for each entry in that account.}}
\par
\pard 
\par
\pard {\cf1{\fs24\f1 This format easily deals with all possible cases that arise in bookkeeping and can accurately represent all possible cases of split transactions and journal entries etc. In my experience so far it overcomes all problems associated with representing the bookkeeping process using a database. There is an overhead in that more accesses to the database may be needed than would be the case using more complex database formats, though I personally believe that for many SME's (small and medium enterprises) and similar organisations, plenty of computer power will be available to facilitate this.}}
\par
\pard 
\par
\pard \qc
{\cf1{\fs36\f1 Future Development}}
\par
\pard 
\par
\pard {\cf1{\fs24\f1 I am developing a set of scripts for bookkeeping purposes using the method described here.}}
\par
\pard 
\par
\pard {\cf1{\fs24\f1 The scripts will initially be written using PHP. Their output will be in HTML. The information will be stored in a database using MySQL. All the software required to run them is available in Open Source form under the GPL (General Public License) or similar license. Further details of this license can be found from the Free Software Foundation. The software can be run on a very wide range of computer equipment and systems including Linux systems. Standard techniques such as use of firewalls, SSL and permissions can be used to ensure that the confidentiality of data is maintained.}}
\par
\pard 
\par
\pard {\cf1{\fs24\f1 The scripts may be made available (once working correctly) under the GPL or a similar license. They will be offered for use, if at all, without any warranty as to fitness for any particular purpose, etc.}}
\par
\pard 
\par
\pard {\cf1{\fs24\f1 I make no promise as to when, if ever, these scripts will be available for others to use, though I already make some use of them myself.}}
\par
\pard 
\par
\pard {\cf1{\fs24\f1 If you want to know more about the progress of this work, are interested in contributing to it, or in making use of it, you can contact me at this address (which has a contact form as well as email and telephone contact details) via the World Wide Web:}}
\par
\pard 
\par
\pard {\cf1{\fs24\f1 http://www.gcircle.co.uk/email/}}
\par
\pard 
\par
\pard {\cf1{\fs24\f1 Kelvin McNulty}}
\par
\pard 
\par
\pard 
\par
\pard 
\par
\pard 
\par
\pard 
\par
\pard 
\par
\pard 
\par }}
--------------Boundary-00=_EID13KTT261LK9SZWL5S--