GridView edit problem If primary key is editable (design problem)
- by Nassign
I would like to ask about the design of table based on it's editability in a Grid View. Let me explain. For example, I have a table named ProductCustomerRel.
Method 1
CustomerCode varchar PK
ProductCode varchar PK
StoreCode varchar PK
Quantity int
Note text
So the combination of the CustomerCode, StoreCode and ProductCode must be unique. The record is displayed on a gridview. The requirement is that you can edit the customer, product and storecode but when the data is saved, the PK constraint must still persist.
The problem here is it would be natural for a grid to be able to edit the 3 primary key, you can only achieve the update operation of the grid view by first deleting the row and then inserting the row with the updated data.
An alternative to this is to just update the table and add a SeqNo, and just enforce the unique constraint of the 3 columns when inserting and updating in the grid view.
Method 2
SeqNo int PK
CustomerCode varchar
ProductCode varchar
StoreCode varchar
Quantity int
Note text
My question is which of the two method is better? or is there another way to do this?
