CODEWITHSUNDEEP
mysqldatabasedatabase-designdatabase-schema
Infrid
Infrid

Reputation: 1

schema and table design with auto_increment field that is not a key

I have an invoice number; this number is associated with a special document. This document has an id, formed by a progressive number and the current year, like this

222-2011
223-2011
224-2011
...

every year the progressive number restarts from 1

1-2012
2-2012
3-2012

At first I thought to make a table with invoice_n, prog_n, year. prog_n is AUTO_INCREMENT and every year I'd reset it. But you cant use an AUTO_INCREMENT field that isn't a key. Anyway, I'm also going to reset the counter, and this is not so recommendable.

I can't change the id format; I have to use that rule. Can I have an efficient design in some way?

the environment is classic LAMP

Upvotes: 2

Views: 342

Answers (2)

Johan
Johan

Reputation: 76537

Expanding on Marius' answer, I'd use a trigger to have MySQL set the invoicenumber automatically like so:

DELIMITER $$

CREATE TRIGGER bi_invoices_each BEFORE INSERT ON invoices FOR EACH ROW
BEGIN
  DECLARE lastest_invoice_number VARCHAR(20);
  DECLARE numberpart INTEGER;

  -- find lastest invoicenumber in the current year.
  SELECT COALESCE(max(invoicenumber),0) INTO lastest_invoice_number 
  FROM invoice 
  WHERE invoice_date >= MAKEDATE(YEAR(NEW.invoice_date)  ,1) 
    AND invoice_date <  MAKEDATE(YEAR(NEW.invoice_date)+1,1);

  -- extract the part before the '-'
  SET numberpart = SUBSTRING_INDEX(lastest_invoice_number,'-',1)

  SET NEW.invoicenumber = CONCAT(numberpart+1,'-',YEAR(NEW.invoice_date));
END $$

DELIMITER ;

Note that you cannot access an auto-incrementing id in a before trigger;
Only in the after trigger can you do this, but there you cannot change any values, so a little trickery is required.
More trickery was used to make sure we use invoice_date as is in the select query so that an index on that field can be used.

See:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_makedate

Upvotes: 3

Marius Ion
Marius Ion

Reputation: 387

You can have a separate id column with auto_increment in your invoices table, and a trigger which fills in prog_n with the following formula : 

prog_n = id - select max(id) from invoices where year = current_year - 1

This way, your prog_n resets automatically each year and you don't need to do it manually. However, it might be a performance issue if you insert a lot of invoices in your table, but I don't think that will happen in practice.

Upvotes: 3

Related Questions