>>what's the right table structures for one-to-many forms?
>...
>>in his opinion, the key shoud be meaningless, not represent anything.
>>
>>while my way (using invoice number) will cause lots of problem in the future, espesially with SQL databases.
>
>There is not a right or wrong here. There are only different opinions. There is not any problem with SQL databases using the PO number as a key. In fact, SAP -- the world's largest ERP software vendor - uses the PO number as the primary key on the PO table. To go further, SAP does not have a dedicated field on any table just for keys -- all tables use the same number for the key as the user will see. For instance, the Material Number in SAP is in the table MARA and field name MATNR, this is a text field of 18 characters. It can be a numeric number that is sequentially increased or can be an alphanumeric sequence. This is up to the customer to configure on how they want the number sequence to be. The user when selecting a particular material number, enters the value for MATNR. This is internally stored for numeric numbering with left padded zeros; however, SAP's logic in finding the record does not need the leading zero's entered by user.
>
>Just because the largest ERP software vendor, SAP, does not have unique dedicated keys does not make it "right" -- it is only one way of doing it.

That is surprising to me. Microsoft's Northwind database had the same design flaw, so maybe SAP simply wanted to be "feature-by-feature competitive." Actually, Northwind also had a combined key somewhere in the design (a key consisting of two concatenated columns.)

The problem with "natural" keys like this is that if you ever allow the end user to change this value through the user interface (and PO number probably should be editable for reasonable flexibility), your system needs to propagate any changes to PO numbers across all of the dependent (child) tables. To make the change safe, you have to wrap the entire change chain in a transaction just in case the propagation is unsuccessful in which case you need to roll back all of the changes so far. It is a very messy way of doing it, especially since surrogate ("meaningless") keys make this type of change completely trivial (assuming that you have normalized your data design so that PO number appears only once in only one table.)

Why would SAP do this is beyond me.