Mysql – Database design for mobile app

data synchronizationdatabase-designdatabase-recommendationMySQLsqlite

At first I would like to say hello to all of you, since it is my first post here on DB Administrators.

I am writing an app which store drinks and their data. At the beginning it was storing all in the local SQLite database and it was all updated by downloading newer version of app by the user. Since DB is still growing, I figured out that it would be nice if user may add a entry himself. So I decided to create MySQL db and sync it with local SQLite. And here are my issues:

  1. How to download only the newer and updated entries, and avoid downloading same entry once again?
  2. How to provide edit entry possibility to db? Is it better to create new table with proposal entries and after verification, move them to main table? Or maybe should I create additional columns, which would be proposals for new values?
  3. What if I would get lots of changes proposals? Is it better to store only the last one? Or maybe block edit possibility if a proposal is already set?
  4. How to avoid cheaters and scammers? Some bored people may want to try make a mess, editing multiple entries with fake data. How to avoid such kind of events? Grant edit access only to verified people?
  5. If I would like to store images of items, is it better to store it in db or use some kind of flicker or instagram instead?
  6. Is it better to save new entry in SQLite instant after edit? Or just post it to MySQL and add it to SQLite only after verification and only via SQLite and MySQL sync?
  7. Is it good idea to set key value to EAN code? At least it is constant and connected to only one product. There will be no items containing no EAN.

Thanks for you replies. And also I would like to apologise if some of my questions doesn't fit in DBA rules. I am not db administrator…. yet 😉

Best Answer

How to download only the newer and updated entries, and avoid downloading same entry once again?

Use a database synchronization tool like SymmetricDS or Daffodil Replicator.

  • You'd have a central database server, like PostgreSQL, with SymmetricDS running on it.

  • Each client, such as an Android smartphone, runs SQLite and SymmetricDS.

  • The SymmetricDS clients and server communicate with each other over HTTP(S), sending changesets back and forth.

How to provide edit entry possibility to db? Is it better to create new table with proposal entries and after verification, move them to main table? Or maybe should I create additional columns, which would be proposals for new values?

I would add an is_approved or similar column that needs to be true for items to show up to regular users. Editor and admins can see unapproved items.

What if I would get lots of changes proposals? Is it better to store only the last one? Or maybe block edit possibility if a proposal is already set?

Use History Tables (copy-on-write to a table similar to your main tables) to capture each change. That way you can rollback to earlier or better versions if you need to. Use Optimistic Concurrency Control to prevent updates if data changed.

How to avoid cheaters and scammers? Some bored people may want to try make a mess, editing multiple entries with fake data. How to avoid such kind of events? Grant edit access only to verified people?

Like this site, add "user abilities" after users have proven themselves. Block bad users. Use machine learning to filter spam.

If I would like to store images of items, is it better to store it in db or use some kind of flicker or instagram instead?

Usually database is better, if you can cache heavily on web server, and can do incremental backup.

Is it better to save new entry in SQLite instant after edit? Or just post it to MySQL and add it to SQLite only after verification and only via SQLite and MySQL sync?

Saving to SQLite locally first allows for offline usage, and is probably more responsive. http://offlinefirst.org/

Is it good idea to set key value to EAN code? At least it is constant and connected to only one product. There will be no items containing no EAN.

No, I would use a meaningless key for product_id and a unique nullable column for EAN.

Related Question