web-erp-developers

Re: Re(2): [Web-erp-developers] Re: Level Description

[Phil D. <ph...@du...>]

Wow Dick, that is some db!
Makes our 30 row table look minuscule... but even if it was 600 million rows
...

Do you think we don't need a unique index on these two fields at all?
Perhaps we just use the application to ensure we don't put in two VAT, Level
1s by mistake. I think it is a good think that the db returns an error and
the error is trapped if a user attempts to insert a duplicate TaxAuthority
and TaxLevel record.

If then you consider that we do need this index to ensure the uniqueness of
each TaxAuthority/Level combination then why would we add another field and
another unique index which would take up additional space - and slow us
down!

Every query we will do on this table will require 2 indexes - one for the
TaxLevel and another for the TaxAuthority or better still - in terms of
query speed -  for some SQL servers a joint/compound index as we have to
identify the rate.

I wouldn't claim to be an SQL guru and certainly without the experience you
have, although I have read widely on database theory and used MS SQL Server,
Sybase Adaptive Server, Oracle, and now MySQL. I have put a fair bit of
thought into the dB structure of web-erp and many have complimented it. I
agree completely in the sense of making the system right by design rather
than kludges later on when the error is too painful to fix correctly.

If you could point me at some reading that shows the error of compound
indexes I would be keen to learn.

The process for returning the appropriate rate for a line item is as
follows:

The TaxAuthority comes from the customer branch CustBranch table.
The TaxLevel comes from the StockMaster.

Both the customer branch and the stock item must be retrieved when an
invoice or credit is being created - the customer/branch is a requirement
before anything can start. The stock items are selected individually and
details retrieved - now with the TaxLevel field. The scripts get the tax
rate from the TaxAuthLevels table there is a function in
SQL_CommonFunctions.inc that does this for each line on the invoice.

Phil


----- Original Message -----
From: "Stins, Dick" <DR...@zi...>
To: "Daintree, Phil" <ph...@du...>
Sent: Wednesday, February 11, 2004 8:07 PM
Subject: Re(2): [Web-erp-developers] Re: Level Description


> Phil,
>
> Ofcourse you are right with the additional input. Next to this you still
need the taxauthlevel table with the rates and some method to tell which
taxauthority is applicable for the item in the warehouse.
>
> I know what indexes are and what the effect is at the storage. I have the
maintenance of a database which exists of tables with over 600 million of
rows and a storage of 50 gb per table exclusive the storage of indexes.
Optimising this is something between science and art.
>
> Since we have the datastructure not completed, you can't see any issues
with storage (=speed).
>
> With best regards,
>
> Dick Stins
>
> -----Original Message-----
> From: Daintree, Phil <ph...@du...>
> To: Web...@li...
<Web...@li...>
> Sent: 11-2-2004 01:36
> Subject: Re: [Web-erp-developers] Re: Level Description
>
>
> > But I see this as a workaround and when things get complicated with lots
> of taxathaurisations with lots of artificial created level to get the
stuff
> done  then are the web-erp users stuck in a very big confusing
> administration.
> > A structural stable solution is to keep a separate table (looks like
your
> other suggestion) with two columns:
> > stockid
> > taxauthlevelid
>
> Think of the additional input required too....
>
> >
> > For this solution you should understand that in this case you have a
very
> storage saving technical primary key taxauthlevelid column compared to the
> two columns primary key
> > taxauthorizationid + level in the taxauthlevel table.
> >
> > Concerning your search for the two columns primary key, I do not
> understand where you find which taxauthority is applicable for the level
> registrated at the stockmaster for a specific stockid. Do I miss
something?
> >
>
> we will only be ever inquiring on this table to get the applicable rate of
> VAT/GST or whatever we call it. For this we will need the TaxAuthority and
> the TaxLevel, it matters not that TaxLevel is in one table and
TaxAuthority
> is in another - the index on both fields is actually an advantage for
faster
> searches in some RDBMS eg SQL Server and Oracle use these indexes. I think
> mysql is pretty good with them too.
>
>
>
>
> -------------------------------------------------------
> The SF.Net email is sponsored by EclipseCon 2004
> Premiere Conference on Open Tools Development and Integration
> See the breadth of Eclipse activity. February 3-5 in Anaheim, CA.
> http://www.eclipsecon.org/osdn
> _______________________________________________
> Web-erp-developers mailing list
> Web...@li...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
> www.Zion-IT.com
> in...@Zi...
>
> Zion-IT b.v.
> Postbus 28048
> 3828 ZG  Hoogland
> tel. + 31 (0) 33 455 13 34
>
> P.S. Nieuw: Hosting van Websites. Hoge kwaliteit, lage prijs. 6,5 euro per
maand voor 100mb schijfruimte, 5000mb bandbreedte + ....
>                     Tijdelijk: eerste basis pagina gratis!!
>

Re: Re(2): [Web-erp-developers] Re: Level Description

[Phil D. <ph...@du...>]

Dick,

I'm getting a little lost ...

Could you make up the SQL for the tables as you think best and the schema
for how it might work and then I can get the whole picture. I am sorry to be
a bit stupid on this. I have in my mind how I think it should work but
clearly this does not work for you. I must understand what you have in mind
and the SQL is much easier than a whole lot of words!!

> Now back to the functional discussion:
> - one taxlevel per stock item is not good enough. This might be only
> appropriate when you have to deal with one and only one taxauthority per
> stock item.
>
> When we decide that one taxauthority is appropriate, we can create a
unique
> index at the level column in the taxauthlevel table. This implies that we
> follow the theory: a unique key level in the authtaxlevel table and a
detail
> level key in the stockmaster.

What I am proposing is as follows:

Tax Authorities
Tax ID  TaxAuthorityDescription
1                VAT
2                Export
3                Customer Pays VAT

the field TaxID has a matching field in CustBranch


TaxAuthLevels

Level    TaxAuthority    Rate
1                1                    0
2                1                    .06
3                1                    .15
1                2                    0
2                2                    0
3                2                    0
1                3                     0
2                3                    0
3                3                    0

The field Level has a matching field in StockMaster

Consider Gouda cheese which is defined as having a tax level 2 in the stock
master. We sell Gouda to a customer's branch in Amsterdam who is set up as
belonging to TaxAuth 1 - VAT.

The tax rate is found:

SELECT Rate FROM TaxAuthLevels WHERE Level=2 AND TaxAuthority=1 - VAT;

and returns 6%.

Consider tulips which are defined as having a tax level 3 in the stock
master. We sell tulips to the same customer in Amsterdam who is set up as
belonging to TaxAuth 1 - VAT.

The tax rate is found:

SELECT Rate FROM TaxAuthLevels WHERE Level=3 AND TaxAuthority=1;

and returns 15%.

We also sell tulips to the same customer but to their branch in Zurich -
these are special none perishable tulips - the Zurich branch is set up as
belonging to TaxAuth 2 - Export.

The tax rate is found:

SELECT Rate FROM TaxAuthLevels WHERE Level=3 AND TaxAuthority=2;

and returns 0%.

So whilst TaxLevel is only one per item in the StockMaster, there are
several in the TaxAuthLevels table it is only in combination with the
TaxAuthority that we get a unique rate - hence the table definition.
TaxLevel in the TaxAuthLevels is not unique.

Still nervous ?

Phil

[Web-erp-developers] Understanding Tax structure

[Phil D. <ph...@du...>]

In the example Gouda is alway TaxLevel 2 - from the single stockmaster
entry.
Tulips always taxLevel 3. These levels are not unique to each stock item -
daffodils would probably also be level 3 and any other item that is taxed in
the same manner as daffodils and tulips. Similarly TaxLevel 2 is for all
those items that should be taxed at 6% VAT for sales within TaxAuthority 1 -
VAT, 0% in TaxAuthority 2 and 0% in TaxAuthority3.


Dick, I have actually already coded all this up and it seems to work ok. I
take your point in that the difficulty is in explaining how it works and how
to set it up - I will start work on that perhaps using some of our examples
from our discussion. Perhaps if I send you the latest scripts together with
our example stock items you may start to feel more comfortable with this? I
am happy to change anything that you're not happy with.

There are a number of changes to the db too so I made up an SQL script to
allow the upgrade from 2.7 to 2.8 more without losing the data. I think I
should make such a script with each new release and update the install
instructions.

What I haven't done is the script to modify the tax rates and adding and
deleting of tax levels -ie the manipulation of the data in TaxAuthLevels.


Phil

----- Original Message -----
From: "Stins, D.R." <d.r...@wo...>
To: "Phil Daintree" <ph...@du...>
Sent: Thursday, February 12, 2004 7:38 PM
Subject: Re: Re(2): [Web-erp-developers] Re: Level Description


> Phil,
>
> I will setup the tables (later)
>
> In you example, what are you doing when Gouda is related in taxauthority 1
> to level 1
> and in taxuthority level 2 and Gouda is related in taxauthority to level
3?
>
> When I understand you quite well you want to setup a special dedicated
level
> for Gouda.
>
> With best regards,
>
> Dick Stins
>
> ----- Original Message -----
> From: "Phil Daintree" <ph...@du...>
> To: "Web ERP Developers" <Web...@li...>
> Sent: Thursday, February 12, 2004 3:33 AM
> Subject: Re: Re(2): [Web-erp-developers] Re: Level Description
>
>
> > Dick,
> >
> > I'm getting a little lost ...
> >
> > Could you make up the SQL for the tables as you think best and the
schema
> > for how it might work and then I can get the whole picture. I am sorry
to
> be
> > a bit stupid on this. I have in my mind how I think it should work but
> > clearly this does not work for you. I must understand what you have in
> mind
> > and the SQL is much easier than a whole lot of words!!
> >
> > > Now back to the functional discussion:
> > > - one taxlevel per stock item is not good enough. This might be only
> > > appropriate when you have to deal with one and only one taxauthority
per
> > > stock item.
> > >
> > > When we decide that one taxauthority is appropriate, we can create a
> > unique
> > > index at the level column in the taxauthlevel table. This implies that
> we
> > > follow the theory: a unique key level in the authtaxlevel table and a
> > detail
> > > level key in the stockmaster.
> >
> > What I am proposing is as follows:
> >
> > Tax Authorities
> > Tax ID  TaxAuthorityDescription
> > 1                VAT
> > 2                Export
> > 3                Customer Pays VAT
> >
> > the field TaxID has a matching field in CustBranch
> >
> >
> > TaxAuthLevels
> >
> > Level    TaxAuthority    Rate
> > 1                1                    0
> > 2                1                    .06
> > 3                1                    .15
> > 1                2                    0
> > 2                2                    0
> > 3                2                    0
> > 1                3                     0
> > 2                3                    0
> > 3                3                    0
> >
> > The field Level has a matching field in StockMaster
> >
> > Consider Gouda cheese which is defined as having a tax level 2 in the
> stock
> > master. We sell Gouda to a customer's branch in Amsterdam who is set up
as
> > belonging to TaxAuth 1 - VAT.
> >
> > The tax rate is found:
> >
> > SELECT Rate FROM TaxAuthLevels WHERE Level=2 AND TaxAuthority=1 - VAT;
> >
> > and returns 6%.
> >
> > Consider tulips which are defined as having a tax level 3 in the stock
> > master. We sell tulips to the same customer in Amsterdam who is set up
as
> > belonging to TaxAuth 1 - VAT.
> >
> > The tax rate is found:
> >
> > SELECT Rate FROM TaxAuthLevels WHERE Level=3 AND TaxAuthority=1;
> >
> > and returns 15%.
> >
> > We also sell tulips to the same customer but to their branch in Zurich -
> > these are special none perishable tulips - the Zurich branch is set up
as
> > belonging to TaxAuth 2 - Export.
> >
> > The tax rate is found:
> >
> > SELECT Rate FROM TaxAuthLevels WHERE Level=3 AND TaxAuthority=2;
> >
> > and returns 0%.
> >
> > So whilst TaxLevel is only one per item in the StockMaster, there are
> > several in the TaxAuthLevels table it is only in combination with the
> > TaxAuthority that we get a unique rate - hence the table definition.
> > TaxLevel in the TaxAuthLevels is not unique.
> >
> > Still nervous ?
> >
> > Phil
> >
> >
> >
> > -------------------------------------------------------
> > SF.Net is sponsored by: Speed Start Your Linux Apps Now.
> > Build and deploy apps & Web services for Linux with
> > a free DVD software kit from IBM. Click Now!
> > http://ads.osdn.com/?ad_id=1356&alloc_id=3438&op=click
> > _______________________________________________
> > Web-erp-developers mailing list
> > Web...@li...
> > https://lists.sourceforge.net/lists/listinfo/web-erp-developers
> >
>
>

Re: [Web-erp-developers] Understanding Tax structure

[Phil D. <ph...@du...>]

I think I see where you are headed. But to clarify, you think it would be
better to be able to have different levels for each item for each tax
authority.

I would agree with this if we were considering sales from locations outside
the single tax authority ie sales from the Paris warehouse compared to the
Mordijk warehouse. Since we are primarily concerned with the accounting for
tax due from sales from Mordijk I tend to think that the solution we have at
the moment is at least adequete. Although I have this nagging feeling that
really we should be considering making it work for sales from many different
tax authorities. I will think fuirther

Phil

----- Original Message -----
From: "Stins, D.R." <d.r...@wo...>
To: "Phil Daintree" <ph...@du...>
Sent: Friday, February 13, 2004 12:08 PM
Subject: Re: [Web-erp-developers] Understanding Tax structure


> Phil,
>
> I will do the script to modify the tax rates and adding and
> deleting of tax levels -ie the manipulation of the data in TaxAuthLevels.
>
> I agree with you to supply with every new release an upgrade script which
> converts the datamodel of the previous version to the datamodel of the new
> version without loosing any data.
>
> It might be an good idea to add an extra table in the datamodel which hold
> the release number to be able to check from which release the current
> datamodel is.
> It would be perfect when the upgrade script refuses to run when the
upgrade
> of that release is not supported by the script.
>
> To prevent lost of data, it is also better to have a separate drop (table)
> script and a separate create table script for a new install.
>
> With best regards,
>
> Dick Stins
>
> ----- Original Message -----
> From: "Phil Daintree" <ph...@du...>
> To: "Web ERP Developers" <Web...@li...>
> Sent: Thursday, February 12, 2004 9:30 PM
> Subject: [Web-erp-developers] Understanding Tax structure
>
>
> > In the example Gouda is alway TaxLevel 2 - from the single stockmaster
> > entry.
> > Tulips always taxLevel 3. These levels are not unique to each stock
item -
> > daffodils would probably also be level 3 and any other item that is
taxed
> in
> > the same manner as daffodils and tulips. Similarly TaxLevel 2 is for all
> > those items that should be taxed at 6% VAT for sales within TaxAuthority
> 1 -
> > VAT, 0% in TaxAuthority 2 and 0% in TaxAuthority3.
> >
> >
> > Dick, I have actually already coded all this up and it seems to work ok.
I
> > take your point in that the difficulty is in explaining how it works and
> how
> > to set it up - I will start work on that perhaps using some of our
> examples
> > from our discussion. Perhaps if I send you the latest scripts together
> with
> > our example stock items you may start to feel more comfortable with
this?
> I
> > am happy to change anything that you're not happy with.
> >
> > There are a number of changes to the db too so I made up an SQL script
to
> > allow the upgrade from 2.7 to 2.8 more without losing the data. I think
I
> > should make such a script with each new release and update the install
> > instructions.
> >
> > What I haven't done is the script to modify the tax rates and adding and
> > deleting of tax levels -ie the manipulation of the data in
TaxAuthLevels.
> >
> >
> > Phil
> >
> > ----- Original Message -----
> > From: "Stins, D.R." <d.r...@wo...>
> > To: "Phil Daintree" <ph...@du...>
> > Sent: Thursday, February 12, 2004 7:38 PM
> > Subject: Re: Re(2): [Web-erp-developers] Re: Level Description
> >
> >
> > > Phil,
> > >
> > > I will setup the tables (later)
> > >
> > > In you example, what are you doing when Gouda is related in
taxauthority
> 1
> > > to level 1
> > > and in taxuthority level 2 and Gouda is related in taxauthority to
level
> > 3?
> > >
> > > When I understand you quite well you want to setup a special dedicated
> > level
> > > for Gouda.
> > >
> > > With best regards,
> > >
> > > Dick Stins
> > >
> > > ----- Original Message -----
> > > From: "Phil Daintree" <ph...@du...>
> > > To: "Web ERP Developers" <Web...@li...>
> > > Sent: Thursday, February 12, 2004 3:33 AM
> > > Subject: Re: Re(2): [Web-erp-developers] Re: Level Description
> > >
> > >
> > > > Dick,
> > > >
> > > > I'm getting a little lost ...
> > > >
> > > > Could you make up the SQL for the tables as you think best and the
> > schema
> > > > for how it might work and then I can get the whole picture. I am
sorry
> > to
> > > be
> > > > a bit stupid on this. I have in my mind how I think it should work
but
> > > > clearly this does not work for you. I must understand what you have
in
> > > mind
> > > > and the SQL is much easier than a whole lot of words!!
> > > >
> > > > > Now back to the functional discussion:
> > > > > - one taxlevel per stock item is not good enough. This might be
only
> > > > > appropriate when you have to deal with one and only one
taxauthority
> > per
> > > > > stock item.
> > > > >
> > > > > When we decide that one taxauthority is appropriate, we can create
a
> > > > unique
> > > > > index at the level column in the taxauthlevel table. This implies
> that
> > > we
> > > > > follow the theory: a unique key level in the authtaxlevel table
and
> a
> > > > detail
> > > > > level key in the stockmaster.
> > > >
> > > > What I am proposing is as follows:
> > > >
> > > > Tax Authorities
> > > > Tax ID  TaxAuthorityDescription
> > > > 1                VAT
> > > > 2                Export
> > > > 3                Customer Pays VAT
> > > >
> > > > the field TaxID has a matching field in CustBranch
> > > >
> > > >
> > > > TaxAuthLevels
> > > >
> > > > Level    TaxAuthority    Rate
> > > > 1                1                    0
> > > > 2                1                    .06
> > > > 3                1                    .15
> > > > 1                2                    0
> > > > 2                2                    0
> > > > 3                2                    0
> > > > 1                3                     0
> > > > 2                3                    0
> > > > 3                3                    0
> > > >
> > > > The field Level has a matching field in StockMaster
> > > >
> > > > Consider Gouda cheese which is defined as having a tax level 2 in
the
> > > stock
> > > > master. We sell Gouda to a customer's branch in Amsterdam who is set
> up
> > as
> > > > belonging to TaxAuth 1 - VAT.
> > > >
> > > > The tax rate is found:
> > > >
> > > > SELECT Rate FROM TaxAuthLevels WHERE Level=2 AND TaxAuthority=1 -
VAT;
> > > >
> > > > and returns 6%.
> > > >
> > > > Consider tulips which are defined as having a tax level 3 in the
stock
> > > > master. We sell tulips to the same customer in Amsterdam who is set
up
> > as
> > > > belonging to TaxAuth 1 - VAT.
> > > >
> > > > The tax rate is found:
> > > >
> > > > SELECT Rate FROM TaxAuthLevels WHERE Level=3 AND TaxAuthority=1;
> > > >
> > > > and returns 15%.
> > > >
> > > > We also sell tulips to the same customer but to their branch in
> Zurich -
> > > > these are special none perishable tulips - the Zurich branch is set
up
> > as
> > > > belonging to TaxAuth 2 - Export.
> > > >
> > > > The tax rate is found:
> > > >
> > > > SELECT Rate FROM TaxAuthLevels WHERE Level=3 AND TaxAuthority=2;
> > > >
> > > > and returns 0%.
> > > >
> > > > So whilst TaxLevel is only one per item in the StockMaster, there
are
> > > > several in the TaxAuthLevels table it is only in combination with
the
> > > > TaxAuthority that we get a unique rate - hence the table definition.
> > > > TaxLevel in the TaxAuthLevels is not unique.
> > > >
> > > > Still nervous ?
> > > >
> > > > Phil
> > > >
> > > >
> > > >
> > > > -------------------------------------------------------
> > > > SF.Net is sponsored by: Speed Start Your Linux Apps Now.
> > > > Build and deploy apps & Web services for Linux with
> > > > a free DVD software kit from IBM. Click Now!
> > > > http://ads.osdn.com/?ad_id=1356&alloc_id=3438&op=click
> > > > _______________________________________________
> > > > Web-erp-developers mailing list
> > > > Web...@li...
> > > > https://lists.sourceforge.net/lists/listinfo/web-erp-developers
> > > >
> > >
> > >
> >
> >
> >
> > -------------------------------------------------------
> > SF.Net is sponsored by: Speed Start Your Linux Apps Now.
> > Build and deploy apps & Web services for Linux with
> > a free DVD software kit from IBM. Click Now!
> > http://ads.osdn.com/?ad_id=1356&alloc_id=3438&op=click
> > _______________________________________________
> > Web-erp-developers mailing list
> > Web...@li...
> > https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>
>