Hello
Serveral times we have spoken about to add an index value to a database table in order to avoid the conflicts in the situation of changing a value of a field which contains to a primary key. Today I was working on how to create some test data sets. Actually I think that my request to change the data base was a little bit to fast.
Actually I’ll follow a thought of mathiasH who has shown me a relative simple way of adding special data sets to the database. And the question has been: What is the appropriate amount of database tables which are necessary to provide for storing a good test case. Therefore I’ve made a small database diagram.
The Tables account, account_property, account_property_code , account_hierarchy and account_hierarchy_detail create a set of table we can name “nested tables”. There are joined to the rest of the world to the tables ledger (by ledger number) currency (by currency code) budget type (by its code) and banking details (by its numerical id). If we add an id to this tables it would be very difficult to run a sql update reduced on the account table set.
Actually I think that our edit problem shall be changed in a something different way. Therefore we only have to know, that a change of a value which provides itself a foreign key can be changed easily until it is referenced the first time from an other table. So we have not to add an id to each table. In our situation either the global tables ledger to banking_details nor the account table set need such an id but only account. But in this case this value must not be used in the program elsewhere.
Let us add a column id which is a unique value created by a time stamp or a random function or else. This column creates the primary key. And then ledger number and account code creates a foreign key. In this case the following SQL command will run:
UPDATE a_currency SET a_ledger_number_c = 'old number', a_accound_code_c = 'new code code value' WHERE id = 123;
That means:
· We have to change nothing in the rest of the program.
· The id is implemented only in those tables where we need id and
· The id is only used local in the db-access area
Furthermore, if this job is done you can delete the constraint from account_property to ledger. The table account_property is a table local to account and account_property code.
If we do this in this way, I can easily create test accounts which can be added to our test database which are compatible (and will ever be!) to all open openpetra installations.
Best regars
Wolfgang
