Database update discussion

[Penko]

My idea came after reading the "Android Merket" topic but have decided to separate the discussion as I am asking generally and don't have any specific problem. Here is a copy of what I wrote in the other topic:

This is a very interesting topic for me too.

I've been thinking - if I implement the version table solution, what would it cause. Let's say my versions are integer - 1,2,3,4,5....

I publish my application with version 1. Then, do some updates and provide version 2, then 3 and so on. What if a user having a version 1 wants to migrate do version 5?

Maybe the best way is to make version-orientated logic.

E.g

in version 2, add "SUM" column to "money" table.

in version 3, add "TOTAL" column to "money table.

in version 4,.5....

Therefore, the function goes through all versions and does the changes one after another.

Another approach, isn't it easier to export user data? With this approach no SQL structure changes will be ever made and it will be only data that you will transfer between databases. But it won't be easy as you will have to build multiple INSERT queries.

What do you think?

 

[timo]

I publish my application with version 1. Then, do some updates and provide version 2, then 3 and so on. What if a user having a version 1 wants to migrate do version 5?

If you database is well planned and version 1 to 5 don't touch the db structure, there is no problem. If you have to change 5 times the db structure, it means that the planning phase was not done as it had to be. It could happend that, sometimes, you know that some data will certainly need to be changed in the future, like rate update, law tax change,etc : in this case 'version' is necessary to prevent installing a new empty db going trough updates.
It's difficult to determine a general behavior; it depends on what kind of application you are doing and if it is for general distribution, for a particular client/company, etc.

Maybe the best way is to make version-orientated logic.
E.g
in version 2, add "SUM" column to "money" table.
in version 3, add "TOTAL" column to "money table.
in version 4,.5....

This is a thing that can only happen if you program targeting a specific Company; in this case it is not a problem because you know who uses your soft.

Another approach, isn't it easier to export user data? With this approach no SQL structure changes will be ever made and it will be only data that you will transfer between databases. But it won't be easy as you will have to build multiple INSERT queries.

Here I don't understand what you mean. Can you make an exampe?

 

[devjet]

I guess what he means is, that before a new version is installed all data is exported,for example as CSV or XML file. Later once the new version is installed all data are imported again.

 

[timo]

Ah..ok. Seems a lot of complication, but it is very difficult to choose a way instead of another if you are not facing a real app.

 

[Penko]

Timo, thanks for posting here, devjet thank you too. Actually, you have understood me very well, I was talking about data export from current database and import to the new one(which comes with the app).

My scenario is that I am working for myself creating paid applications and distributing them via Google Play. I don't agree tha frequent DB changes mean bad planning. Some ideas of mine come exactly between two versions. I started thinking that updating DB structure would be better as you will add one/two fields, a table but nothing more than that.
Particular example - Something which has 4-5 tables and is interacting with all of them. E.g a Fuel program like FuelLog.

Sent from my HTC Desire using Tapatalk

 

[timo]

by adding fields to a table you may face differents scenarios: If the table has static data, you can calculate and fill the new fields of the existing records by code (after version verification), but if the table is a user table, you have to permit null content to existing records (I'm thinking of an agenda table, for example where you add a phone number field). And if the fields are part of a calculation, null fields of old records could be a problem. Not an easy task anyway.

 

[Penko]

I will maybe go for the approach with sql structure changes for each version. It will give me the experience I need.

Sent from my HTC Desire using Tapatalk