Hi Paul, (good to hear from you!) We use (invoice number+date) number as the primary key for the header and (invoice number+body item number+date) in the body of the invoice. We do not allow the same invoice number to be reused on the same day. However they can eventually rollover (ours is a multi location systems with a certain sequence reserved for each location). Body item number a generated sequence.
I agree with paul TIMESTAMP is not a cool thing to use. Generating the sequence (there are many methods of doing this, see the "MySQL Cookbook" by Paul Dubois for some examples including SQL variables or an autoincrement key, program variables, etc) gives a natural and consistant order to the invoice. I hope this helps, Ken > Don't use 2). There is no guarantee a TIMESTAMP value will be unique > among records with the same invoice number. That means invoice number + > timestamp wil not form a PRIMARY KEY. > > I'd use 1), possibly combining the invoice number with the item id as > a composite key. > > > -- > Paul DuBois, Senior Technical Writer > Madison, Wisconsin, USA > MySQL AB, www.mysql.com > > Are you MySQL certified? http://www.mysql.com/certification/ > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
