Re: UnqInvTax issue

[Ap.Muthu]

Should we not have a constant in config/define.php to swap the patch with select 1+1 for such users in the sql_patches.php file?

This way as we move forward, atleast the new users will automatically get regularised.

Alternatively, we can check for failure of the db alteration statement and revert it for such users. A sort of commit/rollback fix.

----- Original Message -----

From: Justin Kelly

Sent: Monday, October 21, 2013 5:58 PM

Subject: Re: UnqInvTax issue

Thanks Ap, appreciate your recent dB work but have left invoice item tax table add non unique - so as to make people can upgrade smoothly

On 18 Oct 2013 20:52, "Ap.Muthu" wrote:

Hi Justin,

 

Even in the case of the tax being a fixed value, it can be entered with mutiple tax_id values like Postage1, Postage2, Handling Charge1, etc.

 

Removing the Uniqueness will detract from database relational integrity.

 

Inclusive solution:

Have a config variable to allow for such a "violation" of these principles - users can choose to enable at their own risk and cannot change it afterwards since the uniqueness would have been violated by then during earlier usage ... and allow it to be false by default for backwards compatibility for the majority of users and those starting off on new installs....

 

Ap.Muthu

 

----- Original Message -----

From: Justin Kelly

Sent: Friday, October 18, 2013 9:38 AM

Subject: Re: UnqInvTax issue

thanks ap - tax rate is not always a tax - can be a fixed $ value - such as handling charge, postage, etc.. - where duplicates may make sense

if we can remove the uniqueness would be appreciated

On Thu, Oct 17, 2013 at 4:57 PM, Ap.Muthu wrote:

I was unaware that the same tax was to be allowed more than once to the same invoice_item_id and am unable to see when that would make sense to any user. In fact this index would help avoid this kind of "error".

 

If inspite of this, you wish to retain this "feature", then we can remove the uniqueness of the index alone. I was wondering how 2 or more instances of say 7% GST applied on a single item in an invoice would look - an error to most users and a feature to maybe a few if any.

 

Such an user can still have another tax_id with possibly the same rate of tax for such a purpose without having to remove the uniqueness and protection from such error it affords.

 

I am trying to make the entire database fully compliant with cascades so that triggers and procedures can be easily implemented (to achieve what php generally does) if needed. Also, if the database becomes fully ERD and UML normalised, then tables such as si_index would be unnecessary.

 

If we stick to this uniqueness, we will be able to do away with having to keep incrementing the id field in the si_invoice_item_tax table when we delete and recreate the invoice_item_tax entries. We will then be able to drop the field primary key id itself and make the (invoice_item_id, tax_id) the compound primary key instead.

----- Original Message -----

From: Justin Kelly

Sent: Thursday, October 17, 2013 10:30 AM

Subject: Re: UnqInvTax issue

in si_invoice_item_tax

- the invoice_item_id and tax_id pair can be duplicated

- invoice_item_id = 1 can have the tax_id = 3 applied a number of times if that makes sense to the user

On Thu, Oct 17, 2013 at 3:06 PM, Ap.Muthu wrote:

Then you must have hit upon an orphan entry in the si_invoice_item_tax table.

Only one specific tax_id must be there for one invoice_item_id,

but one invoice_item_id can have more than one tax_id (only different tax_id values).

Please check the integrity of the si_invoice_items table and the si-invoice_item_tax table.

It is also possible that the si_invoice_items table may have references to si_invoices table where the domain_id may not match since the field domain_id was only introduced now in the si_invoice_items table.

 

Re-ordered the patches 276-278 making 276 come after 277 and 278:

https://github.com/apmuthu/simpleinvoices/commit/aa71ea489fa816f2065eef10a97d61e876355cf8

 

Some PHP wizadry may be needed to be done between the new patches 277 and 278 before application of unique index to the si_invoice_item_tax table especially if there are more than one tax per item.

 

----- Original Message -----

From: Justin Kelly

Sent: Thursday, October 17, 2013 4:57 AM

Subject: UnqInvTax issue

re: https://plus.google.com/118359435432651949322/posts/37rnRzeCdSY

I just tried the update. I get this message: ( it concerns patch 276)

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '22-4' for key 'UnqInvTax'Dude, what happened to your query?:

ALTER TABLE `si_invoice_item_tax` ADD UNIQUE INDEX `UnqInvTax` (`invoice_item_id`, `tax_id`);

Thanks;

[Justin Kelly]

reason i didnt want to add the config thign is to keep things simple

adding more configs and complexities into the db (yes does help to enforce correct data) strays from the goal of being simple