vendredi 22 janvier 2016

Good design for modifying various combination of columns in a table

I have a database table with multiple (~ 15) columns.

I have requirement where where data is updated in a row, in combinations of columns, for the given primary key.

Eg.

Update Col1,Col2,Col3 where PK is something

Update Col4 where PK is something

Update Col5,Col7,Col9,Col10 where PK is something

Update Col5,Col10 where PK is something

I used Python and wrote raw queries to handle such cases. I wrote separate functions to manage each requirements. They look like this :

requirement1( Col5,val1,Col7,val2,Col9,val3,Col10,val4 )
{
    Update TABLE SET (Col5,Col7,Col9,Col10) VALUES (val1,val2,val3,val4)
}

requirement2( Col5,val1,Col10,val2 )
{
    Update TABLE SET (Col5,Col10) VALUES (val1,val2)
}

etc.

But the number of such cases has increased to too many. Is there a good design to handle this ?

Something like a general function :

UpdateTableValue(ColumnName1,ColumnValue1,ColumnName2,ColumnValue2, and so on )
{
    Update TABLE SET (ColumnName1,ColumnName2.. ) VALUES (ColumnValue1,ColumnValue2 .. )
}

And then user could just call :

UpdateTableValue(Col5,val1,Col7,val2,Col9,val3,Col10,val4)
UpdateTableValue(Col7,val,Col3,val2)

P.S. I am not asking for a good DB design to handle this (so no OR mapping etc.). I want to understand a good design from the query wrapper point of view which could handle this.

Aucun commentaire:

Enregistrer un commentaire