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