Re: DB Architecture, 2 cents-worth more

[Dieter S. <dsi...@sq...>]

For what it's worth, I am not combining the customer and vendor tables
because they are two distinct entities which might share the same address
but even that is not always the case. Some may have even noticed that
there is room for one contact person only. This was actually done
delibertly so people wouldn't get the idea that the table can be used for
keeping track of your barber's or dog's name.

As for SL being for the accountant only, not so. In fact you do not need
to know anything about accounting at all. What you do need, is someone who
can configure it for the non-accountant.

The ordering system does not mess with actual accounting data and if one
wanted to setup SL for sending out quotations you can configure it just to
do that. Of course you would want to make a slight adjustment and remove
the 'Create Invoice' link.

See my recent posting about the API and you are all set.

Too complicated, maybe. Out of the box SL looks like a plain vanilla
accounting program, underneath it's a whole different story. Plug in a
webstore or a PIM, now what do you have?


Dieter Simader    http://www.sql-ledger.org   (780) 472-8161
DWS Systems Inc.     Accounting Software       Fax: 478-5281
=========== On a clear disk you can seek forever ===========

On Thu, 3 Jan 2002, alta wrote:

> 
> 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
> 
> 
>