flycaster

I am trying to create a table with a multi-part key of which one part is auto_increment - eg for an order entry system. I need the counter to rest when the other part of the key changes. No matter what I try, MySql (5.0) keeps incrementing the counter regardless of the other field. I have tried with MyIsam and InnoDB.

example:
DROP TABLE IF EXISTS order_detail;
CREATE TABLE order_detail (item smallint NOT NULL AUTO_INCREMENT,
invoice smallint NOT NULL,
description varchar(128),
PRIMARY KEY (item,invoice)) TYPE=InnoDB;


INSERT INTO order_detail VALUES ("","1","screwdriver"),
("","1","wrench"),
("","2","screws");

I should see this
item | invoice | description
1 1 screwdriver
2 1 wrench
1 2 screws

instead I get this
item | invoice | description
1 1 screwdriver
2 1 wrench
3 2 screws

Anybody have any ideas
Thanks
G

Vmusic

Hi,
A 'concatenated' key is a BAD BAD BAD design idea.

Here's how invoices and items work.

An invoice is for one or many items.
An item (spec or type) can be on one or many invoices.

If you create an invoice table as a parent, and invoice_item table as its child you won't have this problem.

The key to the invoice table can be an auto increment.
The key to the item table is can be two columns. The first column is the parent invoice, the second item is simply the line item or invoice item number.

So the first item on invoice #234 has the columns #234 and 1
the second item on invoice #234 has the columns #234 and 2, etc.
You would generate the invoice item number (or line item) from your front end or.... you can select the max item number from the invoice item table where the invoice number = 'x'

You can/should also create a table of 'items'. This is what I referred to as type or spec. This is a simple listing of the 'items' you sell or buy.


Concatenated Keys are a bad, bad thing. It stops you from adapting to change. You write code based on that concatenation and then you're hosed.

Good design Saves Time!!
Vmusic

flycaster

Thanks for the suggestion. I do have two tables and was trying to do what you suggested automatically, rather than manually determine the next line number. I will give that a try. Thanks again.

Flycaster

Vmusic

Flycaster,
You really have two choices:
1) Create a key that is two columns, column being the invoice key and column two being a line item number

2) Create a unique id for each row in the invoice item table regardless of the inovice number. Your invoice item table would be something like

invoice_item_id, display_order, invoice_id, quanity, unit_price, total_price, desc

In this case only the first column, invoice_item_id would be the key.


The problem here is that you would most likely want to keep track of which item was first, second, third, etc anyway for display purposes and to make it easy for the customer or user to identify their order or invoice items.

So if you're going through all of the that, why not choose the first choice above.?




To obtain a simple number for each unique invoice I would suggest
a) SELECT MAX idea to get the next item
b) CODE - Assuming you're using PHP


You can somehow 'reset' autoincrement. But the invoice item, or item number is NOT really unique by itself.

Glad to hear you're not concatenating two separate data elements into one column.

Vmsuic,
Good Design, Saves Time