Basic Database Design Questions

Question

Please feel free to offer improvements to any of my ideas.

My objective is to have multiple users running a desktop program which will pass information to a php script that will then write the information to a database. No need to worry about the details of the desktop application.

My Questions: 1) Will this method be efficient? Or would it be better for the php script to write the data to a text file and then for a cron job to call a php script to process the text file every minute?

2) When I come out with version 2 of the desktop application, how do I adapt the database to handle the new changes? For example, assume v1 of the program just sends one variable. Then v2 of the program sends two variables. I will not be able to make sure all users upgrade to version 2. So if a user is still using version 1 should the php script just write the one variable data to the database and leave the other variable blank?

Another scenario is that what is in version 2 I decide that the original variable needs to be changed? How do I handle that?

Any comments are appreciated!

Answer 1

The efficiency of the PHP script will depend on a couple factors, including how well it's written and how your database is structured. Be particularly careful with indexes; they speed up reads quite a bit, but at the cost of slow down writes -- sometimes dramatically.

I'd go with the live updates until i had reason to change it. If things are done right, clients wouldn't notice either way (except for the delay in seeing the data change, if they can even see that).

Answer 2

Create a versioned web service API and use that.

Notice the emphisis on versioned.

Answer 3

You like to create a rich client. I do not understand what your reasons for php are. If you want to have a client server app I see the following options:

• The desktop app directly accesses a central database
• The desktop app communicates with a web service to hide the database details (e.g. versioning )
• You have php doing all the stuff with the db and use a browser instead of the desktop application.

To handle your second point you should consider using transactions. They guarantee data consistency at every time.