sql-ledger-users — SQL-Ledger users

Re: Database architecture...

[Thomas G. <to...@ad...>]

On Fri, 4 Jan 2002, John O'Gorman wrote:

> 2. re 3NF
>   This is a sound principle which can easily be pushed past the point of
>   absurdity.
>=20
>   For example, why stop at a separate table for addresses? I could (but
> won't)
>   argue that we should properly normalise addresses into 4 subtables:
>    locations -< streets -< towns -< countries on the grounds that this
>   would eliminate enormous redundancy (especially in countries with
> large
>   population and many towns/cities).=20

John, you've neglected post codes!  And what about "neighborhoods"?
Here in New York we have a different "neighborhood" about every 10
meters.  I can't talk to my neighbor as he resides in lowly West
Brighton whereas I live in (posh) Silver Lake.  The two should not
be grouped together just because they share a zip code.  After all
the grass is greener on the other (my) side of the street.

--------------------------------------------------------------------
              Saint Vincent Catholic Medical Centers               =20
--------------------------------------------------------------------
Thomas Good                          tomg@ { admin | q8 } .nrnet.org
Programmer/Analyst                              Phone:  718-818-5528=20
Behavioral Health Services                      Fax:    718-818-5056 =20
Residential Services                            Mobile: 917-282-7359 =20
--------------------------------------------------------------------
/*         Rekordmeister ist nur der FC Bayern M=FCnchen!           */
--------------------------------------------------------------------

Re: Database architecture...

[John O'G. <ogo...@ho...>]

You are both right.

The problem is to know where to stop.

1. re excellence of SQLledger
  I agree that SQLledger is superb. It is much much simpler than
  commercial offerings that I have had to support in the past.
  In general this simplicity is not at the expense of loss of
  function.

  Having a database that you can fully understand because it has only
  a dozen tables is precious.

  Roland's point about increasing database complexity as modules and
  features are added is very apparent in commercial examples where 
  tables are numbered in the many hundreds. I ernestly hope that
  SQL-ledger can avoid falling into this trap. I think Roland's
suggestions
  might help this - but they need to be carefully thought through first.

2. re 3NF
  This is a sound principle which can easily be pushed past the point of
  absurdity.

  For example, why stop at a separate table for addresses? I could (but
won't)
  argue that we should properly normalise addresses into 4 subtables:
   locations -< streets -< towns -< countries on the grounds that this
  would eliminate enormous redundancy (especially in countries with
large
  population and many towns/cities). 

 3. re OOP
  All OOP programming has at its heart the difficulty of deciding how
  to structure your nodel of the problem space. This is almost never
  a single correct or obviuos design.

John O'Gorman

Roland Stoker wrote:
> 
> Hi Thomas...
> 
> This is the last answer to you over the list, for a further discussion I
> suggest we do that privatly....
> 
> I am aware of 3NF, and why you don't always want to follow those rules, I've
> made a lot exceptions on them myself because it can be usefull (mostly for
> performance as you say).
> But helas, thats not my point here. I'm not talking about performance here,
> but about using the possibilities that are offered by open-source software,
> and about keeping possibilyties open for future expansions.
> Most companies would be helped if a software package could be build that
> incorporates al packages they need, using only one database. Take for exemple
> an addresbook. You need the addresses to make invoices, but not everyone has
> acces (or should have) to the accounting, so you'll need a second one for the
> other employees. But if there's a second program that uses the same tables,
> they could use that.
> For that idea to work, you would need a standerd database model, and
> something like addresses would have to be in one table.
> There's also something like loginnames, but I'll react to that later...
> For sql-ledger to really improof from what it is now (it's the best
> bookkeeping program I've seen) is to make it work together with other
> programs and to let the idea go that everything should work inside of it.
> You've now made an order system inside of sql-ledger, but I havn't seen an
> accounting doing the ordering of hardware yet or placing the orders of
> clients. Usually that's someone else, who has no access to the accounting.
> Wouldn't it be great if sql-ledger and phpgroupware could use the same
> database (and i don't mean just the adresbook, but the entire package). With
> enough willpower, it could be done within a month, because it really isn't
> that much work.
> 
> But even if you don't do something like that, the continuing expanding of
> sql-ledger as it is happening now, will mean that in the future there are
> going to be more functions for people and businesses. And creating tables
> every time is going to mean that you're database gets more complex and less
> usefull for companies who want to use all the functionality because they have
> to copy persons all over the database and have diffuculties keeping address
> correct.
> 
> > This is called 3NF (Codd's "Third Normal Form".)  As to it being redundant
> > and the source of 'minor problems' is really a matter of opinion.  Ensuring
> > relational integrity by having each entity describe only ONE subject
> > (whether customer or vendor or foobar) is the practical application of the
> > theory. The entity described by customer is the person's ROLE...this is a
> > distinct role, different from the role of vendor, hence, not really
> > "redundant". Can the same person have two roles?  Yes...does it happen
> > often enough to justify adding complexity to the schema?  Not terribly
> > often.
> 
> I agree it doesn't really happen that often, but that in intself means that i
> DOES happen...???
> 
> > > Other exemple. Say I would like everybody (vendors and customars) to have
> > > a login on my website. With what query do I check the login-name?? I
> > > cannot create a view because then I cannot garantee that a login name
> > > appears only once!
> >
> > This has been discussed previously and is a valid point.  Relational
> > databases use check constraints to prevent corruption.  See the postgres
> > man page on ADD CONSTRAINT or CREATE INDEX...you could also add a stanza or
> > two to the existing code to trap the error before the parser prints stderr
> > to the user's screen.  This is prettier to view than a failed query dumped
> > to the screen (and possibly more useful for the end user.)
> 
> As far as I know that isn't possible. You cannot create an index on a view,
> only on individual tables, which means that a loginname could excist twice,
> once in vendor, and once in customar.
> I think we agree that coding something that checks for that problem isn't the
> ideal solution.
> 
> > > Solution: The solution would be to not take people or companies as being
> > > there functions. I mean, a vendor is not really a vendor because he
> > > himself also buys goods. He is actually a "company" that has a "vendor"
> > > relationship with you.
> > > To recreate that you make a th folowing tables:
> > >
> > > -companies (with the fields like customar or vendor)
> > > -function (a table with company_nr and function_nr, they are primary key)
> > > -function_define (function_nr = primary key, and some explanation fields)
> > > -people (probably something like employees like adres and phone,... but
> > > without salary)
> > > -peoplecompany (a bit like function to tie people to companies, like
> > > contacts) -employee (people_nr, salary,...)
> > > ( You could also start with departments, but is not necessary)
> >
> > And this reduces redundancy?  I don't see it.  From this vantage point it
> > increases complexity without any real gain as to access the data you would
> > need too many joins.  Views would contain subqueries (correlated
> > perhaps)... All of this for the rare exception?   I don't see the gain.
> 
> I'm going from 5 to 6 tables, so not that much compexity extra. If you're
> counting fields it will actually become less. But when there's a new kind of
> company or person, that's when the advantages of this system come into action.
> 
> > It would be easier to simply place every contact in one table and then
> > define their role in the context of a transaction.  But is it necessary?
> > Or even useful?  It makes cursor selects (without joins) difficult when
> > all we want back is a list of vendors...keep it simple is a very good
> > motto.
> 
> There's something called views. If you create a view calle vendor consisting
> of all companies that sell goods to you, you wouldn't even need to change the
> query's. Personly, I don't mind the joins. I have more problems with tables
> that shouldn't be there, making it more diffucult for people trying to make
> add-ons for you're program.
> 
> > > Employee could have a company_nr, but what's the point, I am only
> > > ionterested in my employees so I assume there all mine....
> > > Even adresses can be separated from people and companies if you're really
> > > ambitius.
> >
> > vendor/customer relations may have drill down tables as well...without
> > abandoning the concept of confining each relation to the description of
> > a singular entity (in this case the ROLE of the person recorded in the
> > relation.)  Although here again, it is not a matter of ambition but the
> > usual tradeoff between performance (denormalisation of schema) versus
> > reduction of redundancy (in an effort to cut overhead and eliminate data
> > errors).  In real world situations people often denormalise because the
> > performance gain is worth it.
> 
> I agree that you can have a bad model which gives significant performance
> loss. That's definitly not the case here.
> If you're using an index (which was the idea), the db will search
> logaritmicly. Meaning that that if the tables become twice as long (combining
> vendor's and customars) it will need twice as long.
> Exemple: You have 1 million customars and 1 million vendor's in you're db.
> making the query ( to search a certain vendor using indexnumber) in the db we
> use now using 20 compare statments. The new one will use 42. 42 Compare
> statements is still using no time.
> The performance towards reduction of redundancy you're talking about is
> actually called caching. This is where you deliberatly put data in a table,
> to be able to find it faster later. That's not what were discussing now.
> 
> If you see this sort of thing as too
> > redundant for your taste you might consider the hierarchical model (mumps)
> > as an alternative.  It is very fast, less 'redundant' and has a leaner
> > programming language.  Of course, it has given way to the relational model
> > for some very good reasons (not just Oracle's marketing skills.)
> 
> I'm very fond of the relational system, and for good reason. Even dough I
> don't like using 1 to 3NF
> 
> > Simpler IS better...
> 
> Yes, but what is simpler????
> 
> > > Query's
> > > Queries will not change much, other then always having to define who you
> > > want.
> >
> > [ ... snip ... ]
> >
> > No offence Roland, but what you're talking about is reinventing the wheel.
> > In my view, the current one (being round - and sound) suffices.  Another
> > point is that I've seen alot of people come along and offer suggestions for
> > sweeping changes to a product that has already racked up hours of
> > development time and has a base of satisified users.  Usually not much
> > comes of this. The reason?  The current product does the job...otherwise it
> > would not be in wide use.
> 
> I'm not suggesting anything that would take a lot of time coding. In its
> sompelest form, not even the query's would need work, only the db. If it
> needed a totaly new interface, I would be in the wrong place, because this
> interface is practicly finished and really good.
> 
> Of course, should you wish to reinvent the wheel
> > as an academic exercise, why not?  But I see the existing schema and code
> > as already being rather trim and possessing an object oriented bent.  The
> > combo of a sound relational structure coupled with an OO design is a clear
> > winner.
> 
> Finaly!!!! Of course " a sound relational structure coupled with an OO design
> is a clear winnar". That's what I'm suggesting here. You have an object
> called companies, with certain functions in an interaction with you. That's
> what this is all about. There is no object "vendor" because thats a proparty
> of an object, and not the object itself.
> 
> Roland

Re: DB Architecture, 2 cents-worth more

[Marvin D. <MrM...@se...>]

On Thursday 03 January 2002 14:30, you wrote:
<<snip>>
> I will say that I value SQL-Ledger's focus on accounting.  I think that's
> why it has reached usability so quickly.  Other projects aimed at
> replacement of SAP R/3 (e.g., Linux-Kontor) have struggled for years and
> not produced anything that could be used--thereby, gaining users and an
> incentive to grow and improve.
>
> Matt


IMHO, you hit the nail on the head... Most open source projects do not stay
focused on the basics which is why they fail to produce usable code 
(Linux-Kontor will *never* compete with or replace SAP R/3... There are 
design flaws... I'll be surprised if it ever is completed. All that work... 
And, for what...?...). What it comes down to is you can have the best
idea ever thought of regarding a specific product or thing (In this case 
software), but if you have poor execution, you end up with sh!t and further, 
look like an idiot. On the other hand, you can take the worst idea on earth 
and if you have great execution, you look brilliant: Execution *is* 
everything.

SQL-Ledger is an accounting program (And a good one), not a custom 
application aimed at a specific vertical market. The execution regarding the 
framework of the application as well as the writing of the code has been 
excellent: Just look at the product... I am new to this list... But, what 
originally attracted me to the project was the very fact that Dieter and the 
others responsible for it's development have remained focused on the basics: 
In this case, providing a stable and reliable accouting package. There is an 
API... If people want to add features, well, get busy and write the 
module(s). Find others on the list who want the same features
and get them to help...  


Best


Marvin

RE: Database architecture...

[Sergio M. [GTI] <se...@gt...>]

Hi Roland,

>>Exemple: I am a business that sells goods. I have a vendor where I buy my
>>goods. What happens when he wants one of my goods??? To do it correct, I
>>would have to put him in the customar-table, but that would be creating...
>>
>>Solution: The solution would be to not take people or companies as being
>>there functions. I mean, a vendor is not really a vendor because he
himself ...

Exceptions will always exist and the rule I use when creating databases is
to satisfy the majority.  I also have clients where a vendor is also a
customer, but the percentage is so small that it is not recommended to have
a large table called companies.  This will add complexity to the appliation.

Never do a major impact to database design to satisfy a minority issue!

My .02 cents!
Sergio

Re: DB Architecture, 2 cents-worth more

[<ma...@li...>]

I think the base table layout of SQL-Ledger essentially reflect Dieter's
basic design for the package, and it appears to be serving us well.  At
least, I'm not pursuaded that there's justification for further
decomposition of these tables, either.

OTOH, I wonder if addressing numeric representation issues might not be a
more promising direction for work, for someone knowledgeable about those
issues.  That's not me, I'm afraid.

I will say that I value SQL-Ledger's focus on accounting.  I think that's
why it has reached usability so quickly.  Other projects aimed at
replacement of SAP R/3 (e.g., Linux-Kontor) have struggled for years and
not produced anything that could be used--thereby, gaining users and an
incentive to grow and improve.

Matt


On Thu, 3 Jan 2002, Dieter Simader wrote:

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

-- 

Matt Benjamin

The Linux Box
206 South Fifth Ave. Suite 150
Ann Arbor, MI  48104

tel. 734-761-4689
fax. 734-769-8938
cel. 734-216-5309
pgr. 734-431-0118

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

Re: Database architecture...

[Andrew S. <an...@ne...>]

Here we go again.

Roland Stoker wrote:
> 
> Hi,
> 
> I'm kind of new here, so it's probably not my place to do this but I would
> like to suggest a prety large change in the db-architecture...
> 
> I'll first try to tell why. I've a business that every now and then gets
> asked to build databases. On basis of experience we have found some strange
> irregularities between what people tend to build and what would be more
> convinient.
> 
> Put simple it is about this: Logicly everybody tends to build tables with
> people or companies from there functions in the database. In this one you
> have customer and vendor for example. The fact that you use two tables for
> businesses, is redudancy in it's own and it's not just a theoraticle problem
> because it actually creates (okay, they are minor, but still) problems.
> 
> Exemple: I am a business that sells goods. I have a vendor where I buy my
> goods. What happens when he wants one of my goods??? To do it correct, I
> would have to put him in the customar-table, but that would be creating
> redundancy in itself, because I would have to maintain a second adres and all.
> Other exemple. Say I would like everybody (vendors and customars) to have a
> login on my website. With what query do I check the login-name?? I cannot
> create a view because then I cannot garantee that a login name appears only
> once!
> 
> Solution: The solution would be to not take people or companies as being
> there functions. I mean, a vendor is not really a vendor because he himself
> also buys goods. He is actually a "company" that has a "vendor" relationship
> with you.
> To recreate that you make a th folowing tables:
> 
> -companies (with the fields like customar or vendor)
> -function (a table with company_nr and function_nr, they are primary key)
> -function_define (function_nr = primary key, and some explanation fields)
> -people (probably something like employees like adres and phone,... but
> without salary)
> -peoplecompany (a bit like function to tie people to companies, like contacts)
> -employee (people_nr, salary,...)
> ( You could also start with departments, but is not necessary)
> 
> notes
> Company_nr =1 would be you're company.
> In the function table you could a second company_nr, but I don't think anyone
> would be interessted in relations between two other companies.
> Employee could have a company_nr, but what's the point, I am only ionterested
> in my employees so I assume there all mine....
> Even adresses can be separated from people and companies if you're really
> ambitius.
> 
> Query's
> Queries will not change much, other then always having to define who you want.
> For exemple: select * from customar; becomes select * from companies,
> function, function_define where companies.company_nr = function.company_nr
> and function.function_nr = function_define.function_nr and explanation =
> "customar";
> But now you can get a listing of all companies by: select * from companies;
> 
> Program-changes...
> These can be minor at first, by just changing the query's and here and there
> cvhanging the fields users fill in. This means that you will not be able to
> use the full functionality, but it will work, as long as nobody uses the
> database directly.
> Based on the new database you can add features later.
> 
> User interface
> When you're adding features this will inevitable lead to a different user
> interface. But, I do not think it is going to be more diffcicult, because
> HTML and maybe a little bit of javascript (rather not) you can make the use
> of functions more powerfull.
> I think that longturm this will make expensions possible like CRM (Customar
> Relation Manager). If we all become really ambitius, we could start looking
> at a standerd database-model for companies, which more seperate programs
> (used in companies) could use. For exemple, most employees wouldn't be able
> to use sql-ledger (only the bookkeepers), but most could via an
> adresbookprogram look up the adresses or e-mail from the central database.
> But for that to work the adresses would have to be in one table in a way that
> all programs use.
> You could look at phpgroupware (www.phpgroupware.org) for their applications
> to work with that database-layout.
> 
> All this could create a large program consisting of many modules that every
> company could use. SQL-ledger should be the centerpeace because no company
> can do without accounting.

Large numbers of Customers

[Eric M. <em...@ra...>]

I'm preparing to evaluate SQL-Ledger as a replacement to the accounting
package my wife is using at her office.  I just looked at one of the
demo systems.  I'm curious about how well it scales up to larger
customer databases.  Is there anyone using SQL-Ledger with > 5000
customers?  It seems like the customer selection in the Invoice entry
screen would be unwieldy with more than a few hundred customers.

Re: Opening Balance Equity

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

You can create one GL posting to enter your beginning balances.

Use your last balance sheet.


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, Richard Lyons wrote:

> On Thursday 03 January 2002  4:54, David Wolf wrote:
> > I'm sure this will be a (somewhat?) silly question.. I need to know how 
> > to enter starting balances into the ledger. I tried making a GL entry 
> > that wasn't balanced to open the accounts at '$x'.. but, of course it's 
> > too smart for that ;)
> > 
> > So.. How do I do it? :)
> 
> You should really have an initial balance sheet that is ... in balance.  I 
> haven't got my sql-ledger up and running yet so I don't know if it will 
> accept a whole stack of opening balances in one GL posting, but if it 
> won't, or if (like me) you are disorganized enough to be working through 
> the opening balances piecemeal, you can start a liability account for 
> opening balances and put all the contras in there.  When the opening 
> balance is balanced, that account will be too.  Alternatively, wait a bit 
> for a difinitive answer from some accountancy professional...
> --
> richard
>  
> 
> 

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

Re: Database architecture...

[Roland S. <sql...@st...>]

Hi Thomas...

This is the last answer to you over the list, for a further discussion I 
suggest we do that privatly....

I am aware of 3NF, and why you don't always want to follow those rules, I've 
made a lot exceptions on them myself because it can be usefull (mostly for 
performance as you say).
But helas, thats not my point here. I'm not talking about performance here, 
but about using the possibilities that are offered by open-source software, 
and about keeping possibilyties open for future expansions.
Most companies would be helped if a software package could be build that 
incorporates al packages they need, using only one database. Take for exemple 
an addresbook. You need the addresses to make invoices, but not everyone has 
acces (or should have) to the accounting, so you'll need a second one for the 
other employees. But if there's a second program that uses the same tables, 
they could use that.
For that idea to work, you would need a standerd database model, and 
something like addresses would have to be in one table.
There's also something like loginnames, but I'll react to that later...
For sql-ledger to really improof from what it is now (it's the best 
bookkeeping program I've seen) is to make it work together with other 
programs and to let the idea go that everything should work inside of it. 
You've now made an order system inside of sql-ledger, but I havn't seen an 
accounting doing the ordering of hardware yet or placing the orders of 
clients. Usually that's someone else, who has no access to the accounting.
Wouldn't it be great if sql-ledger and phpgroupware could use the same 
database (and i don't mean just the adresbook, but the entire package). With 
enough willpower, it could be done within a month, because it really isn't 
that much work.

But even if you don't do something like that, the continuing expanding of 
sql-ledger as it is happening now, will mean that in the future there are 
going to be more functions for people and businesses. And creating tables 
every time is going to mean that you're database gets more complex and less 
usefull for companies who want to use all the functionality because they have 
to copy persons all over the database and have diffuculties keeping address 
correct.

> This is called 3NF (Codd's "Third Normal Form".)  As to it being redundant
> and the source of 'minor problems' is really a matter of opinion.  Ensuring
> relational integrity by having each entity describe only ONE subject
> (whether customer or vendor or foobar) is the practical application of the
> theory. The entity described by customer is the person's ROLE...this is a
> distinct role, different from the role of vendor, hence, not really
> "redundant". Can the same person have two roles?  Yes...does it happen
> often enough to justify adding complexity to the schema?  Not terribly
> often.

I agree it doesn't really happen that often, but that in intself means that i 
DOES happen...???

> > Other exemple. Say I would like everybody (vendors and customars) to have
> > a login on my website. With what query do I check the login-name?? I
> > cannot create a view because then I cannot garantee that a login name
> > appears only once!
>
> This has been discussed previously and is a valid point.  Relational
> databases use check constraints to prevent corruption.  See the postgres
> man page on ADD CONSTRAINT or CREATE INDEX...you could also add a stanza or
> two to the existing code to trap the error before the parser prints stderr
> to the user's screen.  This is prettier to view than a failed query dumped
> to the screen (and possibly more useful for the end user.)

As far as I know that isn't possible. You cannot create an index on a view, 
only on individual tables, which means that a loginname could excist twice, 
once in vendor, and once in customar.
I think we agree that coding something that checks for that problem isn't the 
ideal solution.

> > Solution: The solution would be to not take people or companies as being
> > there functions. I mean, a vendor is not really a vendor because he
> > himself also buys goods. He is actually a "company" that has a "vendor"
> > relationship with you.
> > To recreate that you make a th folowing tables:
> >
> > -companies (with the fields like customar or vendor)
> > -function (a table with company_nr and function_nr, they are primary key)
> > -function_define (function_nr = primary key, and some explanation fields)
> > -people (probably something like employees like adres and phone,... but
> > without salary)
> > -peoplecompany (a bit like function to tie people to companies, like
> > contacts) -employee (people_nr, salary,...)
> > ( You could also start with departments, but is not necessary)
>
> And this reduces redundancy?  I don't see it.  From this vantage point it
> increases complexity without any real gain as to access the data you would
> need too many joins.  Views would contain subqueries (correlated
> perhaps)... All of this for the rare exception?   I don't see the gain.

I'm going from 5 to 6 tables, so not that much compexity extra. If you're 
counting fields it will actually become less. But when there's a new kind of 
company or person, that's when the advantages of this system come into action.

> It would be easier to simply place every contact in one table and then
> define their role in the context of a transaction.  But is it necessary?
> Or even useful?  It makes cursor selects (without joins) difficult when
> all we want back is a list of vendors...keep it simple is a very good
> motto.

There's something called views. If you create a view calle vendor consisting 
of all companies that sell goods to you, you wouldn't even need to change the 
query's. Personly, I don't mind the joins. I have more problems with tables 
that shouldn't be there, making it more diffucult for people trying to make 
add-ons for you're program.

> > Employee could have a company_nr, but what's the point, I am only
> > ionterested in my employees so I assume there all mine....
> > Even adresses can be separated from people and companies if you're really
> > ambitius.
>
> vendor/customer relations may have drill down tables as well...without
> abandoning the concept of confining each relation to the description of
> a singular entity (in this case the ROLE of the person recorded in the
> relation.)  Although here again, it is not a matter of ambition but the
> usual tradeoff between performance (denormalisation of schema) versus
> reduction of redundancy (in an effort to cut overhead and eliminate data
> errors).  In real world situations people often denormalise because the
> performance gain is worth it.  

I agree that you can have a bad model which gives significant performance 
loss. That's definitly not the case here.
If you're using an index (which was the idea), the db will search 
logaritmicly. Meaning that that if the tables become twice as long (combining 
vendor's and customars) it will need twice as long.
Exemple: You have 1 million customars and 1 million vendor's in you're db.
making the query ( to search a certain vendor using indexnumber) in the db we 
use now using 20 compare statments. The new one will use 42. 42 Compare 
statements is still using no time.
The performance towards reduction of redundancy you're talking about is 
actually called caching. This is where you deliberatly put data in a table, 
to be able to find it faster later. That's not what were discussing now.

If you see this sort of thing as too
> redundant for your taste you might consider the hierarchical model (mumps)
> as an alternative.  It is very fast, less 'redundant' and has a leaner
> programming language.  Of course, it has given way to the relational model
> for some very good reasons (not just Oracle's marketing skills.)

I'm very fond of the relational system, and for good reason. Even dough I 
don't like using 1 to 3NF

> Simpler IS better...

Yes, but what is simpler????

> > Query's
> > Queries will not change much, other then always having to define who you
> > want.
>
> [ ... snip ... ]
>
> No offence Roland, but what you're talking about is reinventing the wheel.
> In my view, the current one (being round - and sound) suffices.  Another
> point is that I've seen alot of people come along and offer suggestions for
> sweeping changes to a product that has already racked up hours of
> development time and has a base of satisified users.  Usually not much
> comes of this. The reason?  The current product does the job...otherwise it
> would not be in wide use.  

I'm not suggesting anything that would take a lot of time coding. In its 
sompelest form, not even the query's would need work, only the db. If it 
needed a totaly new interface, I would be in the wrong place, because this 
interface is practicly finished and really good.

Of course, should you wish to reinvent the wheel
> as an academic exercise, why not?  But I see the existing schema and code
> as already being rather trim and possessing an object oriented bent.  The
> combo of a sound relational structure coupled with an OO design is a clear
> winner.

Finaly!!!! Of course " a sound relational structure coupled with an OO design 
is a clear winnar". That's what I'm suggesting here. You have an object 
called companies, with certain functions in an interaction with you. That's 
what this is all about. There is no object "vendor" because thats a proparty 
of an object, and not the object itself.

Roland

Re: Database architecture...

[<gha...@fr...>]

On 3 Jan 2002, Martin Lesser wrote:

>                                       Porting data from an existing
> (redundant) db-layout to a new one can be a nightmare.

Have you looked at DBIx::Recordset (possibly combined with
DBIx::Renderer to create tables)?  The only published
documentation I am aware of on this perl module, is to move
an existing set of tables to a new set of tables.

Gord

Re: Database architecture...

[Martin L. <m-l...@le...>]

Roland Stoker <sql...@st...> writes:

> I'm kind of new here, so it's probably not my place to do this but I would 
> like to suggest a prety large change in the db-architecture...

In general you speak about normalization of the db. *If* there are any
plans to modify the layout of the db I agree with you that there should
be spent most time to build a fairly normalized db-design. Perhaps
http://www.phpbuilder.com/columns/barry20000731.php3 is also helpful if
you're interested in db-design.

> Solution: [...] To recreate that you make a th folowing tables:
> 
> -companies (with the fields like customar or vendor)
> -function (a table with company_nr and function_nr, they are primary key)
> -function_define (function_nr = primary key, and some explanation fields)
> -people (probably something like employees like adres and phone,... but 
> without salary)
> -peoplecompany (a bit like function to tie people to companies, like contacts)
> -employee (people_nr, salary,...)

And what if an employee(=people) becomes a company (i.e. a vendor)? IMO
it should not matter wether a person is a natural person or a company -
they should be part of one single table. And this is only one example. A
properly planned db-design is the most important base.

> Queries will not change much, other then always having to define who you want.

Nack. The queries are not the main problem. The content of any existing
db is it, partially due its redundancy. Porting data from an existing
(redundant) db-layout to a new one can be a nightmare.

> Should be something to think about.

With a slightly different approach: Yes.

Martin

Re: Database architecture...

[Thomas G. <to...@ad...>]

On Thu, 3 Jan 2002, Roland Stoker wrote:

> Hi,
>=20
> I'm kind of new here, so it's probably not my place to do this but I woul=
d=20
> like to suggest a prety large change in the db-architecture...
=2E..
> Put simple it is about this: Logicly everybody tends to build tables with=
=20
> people or companies from there functions in the database. In this one you=
=20
> have customer and vendor for example. The fact that you use two tables fo=
r=20
> businesses, is redudancy in it's own and it's not just a theoraticle prob=
lem=20
> because it actually creates (okay, they are minor, but still) problems.

Hi Roland,

This is called 3NF (Codd's "Third Normal Form".)  As to it being redundant
and the source of 'minor problems' is really a matter of opinion.  Ensuring
relational integrity by having each entity describe only ONE subject (wheth=
er
customer or vendor or foobar) is the practical application of the theory.
The entity described by customer is the person's ROLE...this is a distinct
role, different from the role of vendor, hence, not really "redundant".
Can the same person have two roles?  Yes...does it happen often enough to
justify adding complexity to the schema?  Not terribly often.

> Other exemple. Say I would like everybody (vendors and customars) to have=
 a=20
> login on my website. With what query do I check the login-name?? I cannot=
=20
> create a view because then I cannot garantee that a login name appears on=
ly=20
> once!

This has been discussed previously and is a valid point.  Relational databa=
ses
use check constraints to prevent corruption.  See the postgres man page on
ADD CONSTRAINT or CREATE INDEX...you could also add a stanza or two to the
existing code to trap the error before the parser prints stderr to the user=
's
screen.  This is prettier to view than a failed query dumped to the screen
(and possibly more useful for the end user.)

> Solution: The solution would be to not take people or companies as being=
=20
> there functions. I mean, a vendor is not really a vendor because he himse=
lf=20
> also buys goods. He is actually a "company" that has a "vendor" relations=
hip=20
> with you.
> To recreate that you make a th folowing tables:
>=20
> -companies (with the fields like customar or vendor)
> -function (a table with company_nr and function_nr, they are primary key)
> -function_define (function_nr =3D primary key, and some explanation field=
s)
> -people (probably something like employees like adres and phone,... but=
=20
> without salary)
> -peoplecompany (a bit like function to tie people to companies, like cont=
acts)
> -employee (people_nr, salary,...)
> ( You could also start with departments, but is not necessary)

And this reduces redundancy?  I don't see it.  From this vantage point it
increases complexity without any real gain as to access the data you would
need too many joins.  Views would contain subqueries (correlated perhaps)..=
=2E
All of this for the rare exception?   I don't see the gain.

It would be easier to simply place every contact in one table and then
define their role in the context of a transaction.  But is it necessary?
Or even useful?  It makes cursor selects (without joins) difficult when
all we want back is a list of vendors...keep it simple is a very good
motto.

> Company_nr =3D1 would be you're company.
> In the function table you could a second company_nr, but I don't think an=
yone=20
> would be interessted in relations between two other companies.

Huh? =20

> Employee could have a company_nr, but what's the point, I am only iontere=
sted=20
> in my employees so I assume there all mine....
> Even adresses can be separated from people and companies if you're really=
=20
> ambitius.

vendor/customer relations may have drill down tables as well...without=20
abandoning the concept of confining each relation to the description of
a singular entity (in this case the ROLE of the person recorded in the=20
relation.)  Although here again, it is not a matter of ambition but the
usual tradeoff between performance (denormalisation of schema) versus
reduction of redundancy (in an effort to cut overhead and eliminate data
errors).  In real world situations people often denormalise because the
performance gain is worth it.  If you see this sort of thing as too=20
redundant for your taste you might consider the hierarchical model (mumps)
as an alternative.  It is very fast, less 'redundant' and has a leaner
programming language.  Of course, it has given way to the relational model
for some very good reasons (not just Oracle's marketing skills.)
Simpler IS better...

> Query's
> Queries will not change much, other then always having to define who you =
want.

[ ... snip ... ]

No offence Roland, but what you're talking about is reinventing the wheel.
In my view, the current one (being round - and sound) suffices.  Another
point is that I've seen alot of people come along and offer suggestions for
sweeping changes to a product that has already racked up hours of developme=
nt
time and has a base of satisified users.  Usually not much comes of this.
The reason?  The current product does the job...otherwise it would not be
in wide use.  Of course, should you wish to reinvent the wheel as an
academic exercise, why not?  But I see the existing schema and code as
already being rather trim and possessing an object oriented bent.  The comb=
o
of a sound relational structure coupled with an OO design is a clear winner=
=2E

> Should be something to think about.

Good luck with your coding,  I wouldn't mind debating these points
with you over a pint - as I rather like Flemish bier (esp. Duvel!)
Maybe next time I'm in the Rheinland?

Cheers,
Thomas

--------------------------------------------------------------------
              Saint Vincent Catholic Medical Centers               =20
--------------------------------------------------------------------
Thomas Good                          tomg@ { admin | q8 } .nrnet.org
Programmer/Analyst                              Phone:  718-818-5528=20
Behavioral Health Services                      Fax:    718-818-5056 =20
Residential Services                            Mobile: 917-282-7359 =20
--------------------------------------------------------------------
/*         Rekordmeister ist nur der FC Bayern M=FCnchen!           */
--------------------------------------------------------------------

RE: Database architecture...

[Chris P. <ch...@ce...>]

I think you might be offering to contribute some of your developer time to
help Dieter build the next version - I'm sure he's willing to add people to
the project with the suitable skills and time...

-----Original Message-----
From: Roland Stoker
To: sql-ledger users mailing list
Sent: 1/3/02 10:43 AM
Subject: Database architecture...

Hi,

I'm kind of new here, so it's probably not my place to do this but I
would 
like to suggest a prety large change in the db-architecture...

I'll first try to tell why. I've a business that every now and then gets

asked to build databases. On basis of experience we have found some
strange 
irregularities between what people tend to build and what would be more 
convinient.

Put simple it is about this: Logicly everybody tends to build tables
with 
people or companies from there functions in the database. In this one
you 
have customer and vendor for example. The fact that you use two tables
for 
businesses, is redudancy in it's own and it's not just a theoraticle
problem 
because it actually creates (okay, they are minor, but still) problems.

Exemple: I am a business that sells goods. I have a vendor where I buy
my 
goods. What happens when he wants one of my goods??? To do it correct, I

would have to put him in the customar-table, but that would be creating 
redundancy in itself, because I would have to maintain a second adres
and all.
Other exemple. Say I would like everybody (vendors and customars) to
have a 
login on my website. With what query do I check the login-name?? I
cannot 
create a view because then I cannot garantee that a login name appears
only 
once!


Solution: The solution would be to not take people or companies as being

there functions. I mean, a vendor is not really a vendor because he
himself 
also buys goods. He is actually a "company" that has a "vendor"
relationship 
with you.
To recreate that you make a th folowing tables:

-companies (with the fields like customar or vendor)
-function (a table with company_nr and function_nr, they are primary
key)
-function_define (function_nr = primary key, and some explanation
fields)
-people (probably something like employees like adres and phone,... but 
without salary)
-peoplecompany (a bit like function to tie people to companies, like
contacts)
-employee (people_nr, salary,...)
( You could also start with departments, but is not necessary)

notes
Company_nr =1 would be you're company.
In the function table you could a second company_nr, but I don't think
anyone 
would be interessted in relations between two other companies.
Employee could have a company_nr, but what's the point, I am only
ionterested 
in my employees so I assume there all mine....
Even adresses can be separated from people and companies if you're
really 
ambitius.

Query's
Queries will not change much, other then always having to define who you
want.
For exemple: select * from customar; becomes select * from companies, 
function, function_define where companies.company_nr =
function.company_nr 
and function.function_nr = function_define.function_nr and explanation =

"customar";
But now you can get a listing of all companies by: select * from
companies;

Program-changes...
These can be minor at first, by just changing the query's and here and
there 
cvhanging the fields users fill in. This means that you will not be able
to 
use the full functionality, but it will work, as long as nobody uses the

database directly.
Based on the new database you can add features later.

User interface
When you're adding features this will inevitable lead to a different
user 
interface. But, I do not think it is going to be more diffcicult,
because 
HTML and maybe a little bit of javascript (rather not) you can make the
use 
of functions more powerfull.
I think that longturm this will make expensions possible like CRM
(Customar 
Relation Manager). If we all become really ambitius, we could start
looking 
at a standerd database-model for companies, which more seperate programs

(used in companies) could use. For exemple, most employees wouldn't be
able 
to use sql-ledger (only the bookkeepers), but most could via an 
adresbookprogram look up the adresses or e-mail from the central
database. 
But for that to work the adresses would have to be in one table in a way
that 
all programs use.
You could look at phpgroupware (www.phpgroupware.org) for their
applications 
to work with that database-layout.

All this could create a large program consisting of many modules that
every 
company could use. SQL-ledger should be the centerpeace because no
company 
can do without accounting.

Should be something to think about.

Roland Stoker

Database architecture...

[Roland S. <sql...@st...>]

Hi,

I'm kind of new here, so it's probably not my place to do this but I would 
like to suggest a prety large change in the db-architecture...

I'll first try to tell why. I've a business that every now and then gets 
asked to build databases. On basis of experience we have found some strange 
irregularities between what people tend to build and what would be more 
convinient.

Put simple it is about this: Logicly everybody tends to build tables with 
people or companies from there functions in the database. In this one you 
have customer and vendor for example. The fact that you use two tables for 
businesses, is redudancy in it's own and it's not just a theoraticle problem 
because it actually creates (okay, they are minor, but still) problems.

Exemple: I am a business that sells goods. I have a vendor where I buy my 
goods. What happens when he wants one of my goods??? To do it correct, I 
would have to put him in the customar-table, but that would be creating 
redundancy in itself, because I would have to maintain a second adres and all.
Other exemple. Say I would like everybody (vendors and customars) to have a 
login on my website. With what query do I check the login-name?? I cannot 
create a view because then I cannot garantee that a login name appears only 
once!


Solution: The solution would be to not take people or companies as being 
there functions. I mean, a vendor is not really a vendor because he himself 
also buys goods. He is actually a "company" that has a "vendor" relationship 
with you.
To recreate that you make a th folowing tables:

-companies (with the fields like customar or vendor)
-function (a table with company_nr and function_nr, they are primary key)
-function_define (function_nr = primary key, and some explanation fields)
-people (probably something like employees like adres and phone,... but 
without salary)
-peoplecompany (a bit like function to tie people to companies, like contacts)
-employee (people_nr, salary,...)
( You could also start with departments, but is not necessary)

notes
Company_nr =1 would be you're company.
In the function table you could a second company_nr, but I don't think anyone 
would be interessted in relations between two other companies.
Employee could have a company_nr, but what's the point, I am only ionterested 
in my employees so I assume there all mine....
Even adresses can be separated from people and companies if you're really 
ambitius.

Query's
Queries will not change much, other then always having to define who you want.
For exemple: select * from customar; becomes select * from companies, 
function, function_define where companies.company_nr = function.company_nr 
and function.function_nr = function_define.function_nr and explanation = 
"customar";
But now you can get a listing of all companies by: select * from companies;

Program-changes...
These can be minor at first, by just changing the query's and here and there 
cvhanging the fields users fill in. This means that you will not be able to 
use the full functionality, but it will work, as long as nobody uses the 
database directly.
Based on the new database you can add features later.

User interface
When you're adding features this will inevitable lead to a different user 
interface. But, I do not think it is going to be more diffcicult, because 
HTML and maybe a little bit of javascript (rather not) you can make the use 
of functions more powerfull.
I think that longturm this will make expensions possible like CRM (Customar 
Relation Manager). If we all become really ambitius, we could start looking 
at a standerd database-model for companies, which more seperate programs 
(used in companies) could use. For exemple, most employees wouldn't be able 
to use sql-ledger (only the bookkeepers), but most could via an 
adresbookprogram look up the adresses or e-mail from the central database. 
But for that to work the adresses would have to be in one table in a way that 
all programs use.
You could look at phpgroupware (www.phpgroupware.org) for their applications 
to work with that database-layout.

All this could create a large program consisting of many modules that every 
company could use. SQL-ledger should be the centerpeace because no company 
can do without accounting.

Should be something to think about.

Roland Stoker

Re: Payroll progress?? => commision

[Oscar B. <os...@el...>]

Hi Steve,

I was just wondering how you plan o calculate the commisions.
My view (but I guess that there are many possibilities) is that it would 
be based on either;
- a percentage of the turnover generated by the employee per month
- a percentage of the profit margin ( ( sell-cost) * % ) generated by 
the employee per month

Are you looking to do something like this (tracking the sales people) or 
are you having something else in mind?

Thanks for letting me know.

Regards,

Oscar



Steve Doerr wrote:

> Hi.  I have a copy of the payroll features at (please contact me for a
> tarball):
> 
> http://business.dynodns.net/cgi-bin/sql-ledger-alpha/sql-ledger/login.pl
> 
> I haven't updated the menu for 1.8 yet, and it needs the following:
> 
> 1.  Search/edit code
> 2.  uid fixed in deduction setup
> 3.  fix adding more than one deduction per employee
> 4.  change commission/overtime fields to drop down (same logic as
> deductions)
> 5.  allow multiple commission/overtime rates per employee (same logic as
> deductions)
> 6.  pay period entry screen and post routine
> 7.  a little synching w/ 1.8 (stylesheets, auth menu, etc.)
> 
> Dieter has code elsewhere in SL that can be adapted to do most
> everything that needs to be done.
> I'm going to finish the rest of the screens and start stubbing in
> functions over the next few weeks.
> 
> This is a big extension of SL.  It will probably end up having 1/4 to
> 1/3 as much code as the rest of SL.  I'm a professional accountant and
> an amateur programmer, so I've not been able to solve my coding problems
> as quickly as I would like.
> 
> If anyone interested in payroll function thinks they could work with
> what I've done, please let me know.
> 
> Steve
> 
> Oscar Buijten wrote:
> 
> 
>>Hi!
>>
>>I was just wondering if the payroll development is progressing.
>>Any news on this one?
>>
>>Thanks,
>>
>>Oscar
>>
> 


-- 
_____________________________________________________________

Oscar Buijten

Tel: +33.4.67.57.97.45
Fax: +33.4.67.57.97.46
GSM: +33.6.20.84.15.22

Email: os...@el...

Web:   www.elbie.com

Re: Opening Balance Equity

[Richard L. <ri...@th...>]

On Thursday 03 January 2002  4:54, David Wolf wrote:
> I'm sure this will be a (somewhat?) silly question.. I need to know how 
> to enter starting balances into the ledger. I tried making a GL entry 
> that wasn't balanced to open the accounts at '$x'.. but, of course it's 
> too smart for that ;)
> 
> So.. How do I do it? :)

You should really have an initial balance sheet that is ... in balance.  I 
haven't got my sql-ledger up and running yet so I don't know if it will 
accept a whole stack of opening balances in one GL posting, but if it 
won't, or if (like me) you are disorganized enough to be working through 
the opening balances piecemeal, you can start a liability account for 
opening balances and put all the contras in there.  When the opening 
balance is balanced, that account will be too.  Alternatively, wait a bit 
for a difinitive answer from some accountancy professional...
--
richard
 

4 Digit Decimals?

[Roger S. <rs...@ma...>]

I am using SQL-Ledger 1.8.0... is there a way to change the
amount stored in the tables to be 4 decimal digits long? On any
table... I've done some research and found out you can only
declare the type of a column in PostGreSQL whenever you add a
new one. Is my assumption correct? or is there a way around
this... any help in this direction is greatly welcomed and
apprecited!

Roger Samour

RE: Payroll progress??

[<ma...@ds...>]

Good job Steve!

I'm currently using Dynacom accounting package, www.dynacom.ca and went t=
o
get a snapshot of the payroll screen.  Please use this to help capture al=
l
information/fields that may be needed for SL-Payroll module.

Employee Screen:
+------------------------------------------------------------------------=
---
+
=A6        Code =10 JS
=A6
=A6                                               Prov. of Emp. =10 ON
=A6
=A6        Name =10 JOHN SMITH                      Soc. Ins. No. =10 251=
 582 359
=A6
=A6     Address =10 5468, Main Avenue               Date of Birth =10 65.=
03.12
=A6
=A6             =10 Toronto (Ontario)               Date Started  =10 94.=
01.01
=A6
=A6  Post. Code =10 L3E 1C9                         Date Ended    =10   .=
  .
=A6
=A6   Telephone =10 (416) 856-8956                  Curr. Status  =10 A
=A6
=A6
=A6
=A6  Occupation =10 Director                         Tax Exempt ? =10 No
=A6
=A6 Pension No. =10                                  CPP Exempt ? =10 No
=A6
=A6     Comment =10 Can Work On Saturdays             EI Exempt ? =10 No
=A6
=A6
=A6
=A6  Pay Per. =10 52        TD1-TC =10       TP1015.3-TC =10           F1=
  =10
=A6
=A6  Hrs/Week =10 40.00     TD1-AD =10       TP1015.3-AD =10           K3=
  =10
=A6
=A6  Hr. Rate =10  12.500   TD1X-R =10       TP1015.3-OD =10           LC=
F =10
=A6
=A6  An. Sal. =10  26,000   TD1X-E =10       TP1015.R.13.1           LCP =
=10
=A6
=A6                                                              Y   =10
=A6
+------------------------------------------------------------------------=
---
+

Payroll Transaction Code screen:
+------------------------------------------------------------------------=
---
---+
=A6 Code =10 BON                           Description =10 Bonus
=A6
=A6 Type =10 E  Earning                Print on Stub ? =10 Yes
=A6
=A6 -------------------------- TRX AMOUNT
CALCULATION -------------------------- =A6
=A6  Calcul. Method =10 1  Manual or Fixed Entry        Accumulator No =10
=A6
=A6  Fixed Amt (FA) =10      0.00                       Minimum Amount =10
0.00 =A6
=A6       Rate (R%) =10   0.000                         Maximum Amount =10
99,999.99 =A6
=A6
=A6
=A6 ----------- EARNING / BENEFIT ------------  ---------
ACCUMULATORS --------- =A6
=A6 Earning Type    =10 B       Benefit Type =10    1. Number of Hours Re=
gular   =10
N =A6
=A6 Units =3D Hours ? =10 N       Deduct EI  ? =10 Y  2. Number of Hours =
Overtime  =10
N =A6
=A6 Txble Earning ? =10 Y       CPP or QPP ? =10 Y  3. Group Insurance   =
        =10
N =A6
=A6                           QHIP Contr ? =10 Y  4. Vacation Pay        =
      =10
N =A6
=A6 --------------- DEDUCTION ----------------  5. <Not Defined>
=A6
=A6 Type of Deduction =10                         6. <Not Defined>
=A6
=A6                                             7. <Not Defined>
=A6
=A6 -------------- G/L ACCOUNTS --------------  8. <Not Defined>
=A6
=A6  Debit =10 624000  Gross Salaries             9. <Not Defined>
=A6
=A6 Credit =10 111100  Bank Account              10. <Not Defined>
=A6
+------------------------------------------------------------------------=
---
---+

Note, this package is used all over Quebec and Ontario.

Take care...
Sergio

Opening Balance Equity

[David W. <di...@tr...>]

I'm sure this will be a (somewhat?) silly question.. I need to know how 
to enter starting balances into the ledger. I tried making a GL entry 
that wasn't balanced to open the accounts at '$x'.. but, of course it's 
too smart for that ;)

So.. How do I do it? :)

One other question I have.. There seems to be many copies of the same 
files in the distribution. I get the bin/ directory with the user agent 
info, etc.. But, what I'd like to know is, what are the differences 
between the files, and when is one used (in one particular dir) instead 
of another in, say the SL directory?

Thanks

David

4 Digit Sale Amounts?

[Roger S. <rs...@ma...>]

I am using SQL-Ledger 1.8.0... is there a way to change the amount stored in the tables to be 4 digits long? On any table... I've done some research and found out you can only declare the type of a column in PostGreSQL whenever you add a new one. Is my assumption correct? or is there a way around this... any help in this direction is greatly welcomed and apprecited!

Roger Samour

Re: Payroll progress??

[Steve D. <sd...@sw...>]

Hi.  I have a copy of the payroll features at (please contact me for a
tarball):

http://business.dynodns.net/cgi-bin/sql-ledger-alpha/sql-ledger/login.pl

I haven't updated the menu for 1.8 yet, and it needs the following:

1.  Search/edit code
2.  uid fixed in deduction setup
3.  fix adding more than one deduction per employee
4.  change commission/overtime fields to drop down (same logic as
deductions)
5.  allow multiple commission/overtime rates per employee (same logic as
deductions)
6.  pay period entry screen and post routine
7.  a little synching w/ 1.8 (stylesheets, auth menu, etc.)

Dieter has code elsewhere in SL that can be adapted to do most
everything that needs to be done.
I'm going to finish the rest of the screens and start stubbing in
functions over the next few weeks.

This is a big extension of SL.  It will probably end up having 1/4 to
1/3 as much code as the rest of SL.  I'm a professional accountant and
an amateur programmer, so I've not been able to solve my coding problems
as quickly as I would like.

If anyone interested in payroll function thinks they could work with
what I've done, please let me know.

Steve

Oscar Buijten wrote:

> Hi!
>
> I was just wondering if the payroll development is progressing.
> Any news on this one?
>
> Thanks,
>
> Oscar

Re: DBI

[Roderick A. A. <raa...@ti...>]

On Wed, 2 Jan 2002, Richard Lyons wrote:

> RPMs: postgresql-devel-7.0.3-8.i386.rpm  (I needed this cos of deps.)
>       perl-DBD-Pg-0.95-1.i386.rpm
>       perl-DBI-1.14-10.i386.rpm (thanks to those here who pointed 
>                                   me to these)
> RPMs installed for me quite happily (I had already installed 
> postgresql).

This is getting to be an 'old' version of PostgreSQL  :-)  The current
version is 7.1.3 with 7.2 to be released soon.  This should work fine
for SQL-Ledger but after you get a chance to play with it look into
upgrading to the then current version.  Features and performance is
increasing quickly.

 

Cheers,
Rod
-- 
                      Let Accuracy Triumph Over Victory

                                                       Zetetic Institute
                                                        "David's Sling"
                                                         Marc Stiegler

Re: DBI

[Richard L. <ri...@th...>]

On Wednesday 02 January 2002 20:09, you wrote:
> Although the following steps are for SuSE 7.3, I believe some of the 
> details may help you get through the installation on Red Hat, as well:

Well...  This was timely as I was stuck about there.  BUT:  RedHat is 
different.  As far as I have discovered the differences are:

 
 INSTALLATION INSTRUCTIONS: 
 PostgreSQL and Perl DBD on RH7.1 versus SuSE 7.3
 
> 1. <snip> instead of YaST2, RH has

RPMs: postgresql-devel-7.0.3-8.i386.rpm  (I needed this cos of deps.)
      perl-DBD-Pg-0.95-1.i386.rpm
      perl-DBI-1.14-10.i386.rpm (thanks to those here who pointed 
                                  me to these)
RPMs installed for me quite happily (I had already installed 
postgresql).


> 2. Setup directories and paths for PostgreSQL:
> 
> su -     (change to root)               ;
>                                         ; I didn't need to do
>   Setup disk directory for your db:     ; all this -- the directory
> # mkdir /var/lib/pgsql/data             ; was already there and 
> # chown postgres /var/lib/pgsql/data    ; chowned (I suppose by rpm)

> - Put PostgreSQL path /usr/bin/psql     ; not sure abt this. RH has
> into PATH in /etc/profile.local         ; no /etc/profile.local. There
                                          ; is /etc/profile, but I opted
                                          ; not to fiddle with this     
                                          ; shell script unless it      
                                          ; proves necessary.
 
> 3. Initialize the database, start it, and test it:
> 
> # /usr/local/pgsql/bin/initdb -D /var/lib/pgsql/data ; it wouldn't
                                                       ; let me: db
                                                       ; already exists

> # rcpostgresql start                    ; in RH that is (I believe)
                                          ; # service postgresql start

>   Quick-test the server:
> # su postgres                           ; something very similar was
> > psql -d template1                     ; fine on my RH system
>   \q to exit psql

>   Setup another db user:
> > createuser <db-user>     <snip>       ; no problem here either 
 
> 4. Verify the existence of the following, 
> which are required for the DBD:
> 
> - /usr/include/pgsql/libpq-fe.h         ; I don't have this
> - /usr/lib/libpq.so                     ; I do have this
> 
> 5. Download DBD <snip>                  ; already done as rpm         
        
> 6. to 9. <large snip>                   ; not relevant, therefore

So it looked as though I had it easier than those with SuSE in this 
instance.  BUT (there is always a 'but'...)
               When I try to connect to 
               http://localhost/sql-ledger/admin.pl
               I get "Error.  Permission denied: users/members"

So something ain't right yet.  Anyone help me out here?

--
richard