DB Architecture, 2 cents-worth more

[alta <al...@al...>]

First, let me say that I do not wish to encourage quick changes to 
the database based on features that are important just for me.  
sql-ledger is progressing nicely, and I would rather adapt my way of 
doing things than upset the apple cart.  That being said and for sake 
of discussion, I have found the following fields work well for 
building a general-use database:

Customer/Vendor Table:

- Separate fields for title (Mr.), firstname, middlei, lastname, 
city, zip, country.  The existing approach of combining the fields is 
fine for accounting-only, but creates problems for other business 
programs that might access the database.

- Unique visible customer id or key.  ( I use an 8-char alpha code 
that has been designed to have meaning for me, so I can easily locate 
a customer in a large database.)  In sql-ledger I use the name field 
for this key.  Also, this key is easier to link to other preexisting 
databases than a hidden integer key that is automatically generated 
(as in sql-ledger).

- A general-purpose flag feld that can be used by external programs 
for selecting.

- A category code for each, that would indicate the type of customer 
or vendor.  I use a several-character code that aids in sending mass 
mailings, etc.

- Dates: row_created, row_changed .  I currently stuff these into the 
shipto name field.

- Several miscellaneous fields for custom use.

Invoices:

- Save actual bill_to and ship_to addresses with the transaction.  
Allow editing these.  Therefore, if the address changes in the 
customer database, you will always be able to look back in history 
and see the actual addresses that were used for the transaction.

- Text fields for both private notes (not printed on the invoice), 
and message notes that are printed on the invoice and packing slip.


I share these thoughts for anyone who might be interested.  They were 
in my previous MS Acces system.  However, I am so pleased to have a 
nice perl accounting system to work with that the above shortcomings 
(from my perspective) are not a serious problem for me.  If 
necessary, I can create auxiliary tables and write perl code to 
populate them. 

... Reed

-- 
Reed White  -  ALTA RESEARCH  -  www.alta-research.com
Phone: 877-360-2582  -  Email: al...@al...