Let's say I'm selling 3 products. I have a products table with id and name (so that table would have 3 entries). I want each product to have a serial number, each independent from one another, but I don't want to create a table for each product as the product count might grow.
How can I achieve this so that the serial number auto increments for each product? I figured I would need some pivot table but that requires me to make 3 pivot tables for each product.
Best Answer
From here: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.
Which returns:
In this case (when the AUTO_INCREMENT column is part of a multiple-column index), AUTO_INCREMENT values are reused if you delete the row with the biggest AUTO_INCREMENT value in any group. This happens even for MyISAM tables, for which AUTO_INCREMENT values normally are not reused.