2

I have some basic questions about mobile app database management. I have searched for it but can not find answers to my questions.

Suppose that I have a Sports table including some sport names which will be same for every user. When a user does a sport, the workout will be added to the Workouts table. So, Workouts table is spesific for every user.

1. Tables in a user's phone

enter image description here So, what if I want to add 1 more sport in Sports table and release an update? I can update Sports table in onUpgrade method.

2. New version of Sports table

enter image description here

Question: Do Workouts tables in everyone's phone change according to the new Sports table? (Remember that Workouts table is different for every user)

enter image description here

I know this was a really long post for this question but I wanted to explain everything. Thank you in advance.

Zapdos
  • 617
  • 1
  • 6
  • 23

1 Answers1

0

The OnUpgrade method of your SqlOpenHelper is called when you update the schema of your database not when you add more data to an existing table. I guess you could use it to version your data but to me, that seems rather a strange way to do it.

Schema changes are for example:

  • Add a column to a table.
  • Add a table.
  • Remove a column from table. etc.

Whatever code you run in OnUpgrade will determine the structure of the database on a device. It is all up to you.

Also, adding data to the Sports table would not affect the workouts table at all (assuming you are not using triggers)

Kuffs
  • 35,581
  • 10
  • 79
  • 92
  • onUpgrade method is called when DB version is changed. I am not asking it. If I make a change at Sports table and upgrade the DB version, and then release my app again, do workouts table change itself? Because there is NO way to change a table that is different at everybodies phone. – Zapdos Jun 23 '17 at 13:49
  • Yes that is correct. What I meant was you should change the DB version when you change the schema not when you add data. The workouts table will only change if YOU change it. – Kuffs Jun 23 '17 at 13:51
  • But what is the purpose of defining the foreign key? When a Sports table's row's primary key changed, shouldn't Workout tables foreign key's change at all rows? – Zapdos Jun 23 '17 at 13:54
  • 1
    I think I understand what you are asking now. A primary key is not supposed to be changed. Once set, it should always remain the same especially if you are using it in a primarykey\foreignkey relationship. If you want to add a new row to a table, add it with a new currently unused key. Do not change all of the other keys or you will likely cause yourself no end of problems. – Kuffs Jun 23 '17 at 13:57
  • 1
    See: https://stackoverflow.com/questions/3838414/can-we-update-primary-key-values-of-a-table – Kuffs Jun 23 '17 at 14:00
  • If you really REALLY must update your PK, see https://sqlite.org/foreignkeys.html specifically the section relating to `ON UPDATE CASCADE` – Kuffs Jun 23 '17 at 14:04
  • Thank you but suppose that I don't change primary keys anymore. But what if I delete a row in Sports table? In this case, how can I update Workouts table? – Zapdos Jun 23 '17 at 14:09
  • 1
    well when deleting a row from Sports, you can also manually and hopefully within a transaction, delete all matching rows in Workouts OR you can see the link but this time look for the `ON DELETE` section. https://sqlite.org/foreignkeys.html#fk_actions – Kuffs Jun 23 '17 at 14:11