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

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.

Answer 2

Create a versioned web service API and use that.

Notice the emphisis on versioned.

Answer 3

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).