1

The few LAMP web applications I have installed require me to create the necessary database tables myself, with a schema file, instead of doing it automatically with a script or setup page. Is this a normal and accepted practice? In my own hobby projects I find it much easier and hassle free to just create the tables in code. As the programmer I can make sure it's done correctly, and as the user I don't care - just make it work!

However, I have very limited knowledge and experience in the world of web applications and database management, so I don't know the pros and cons of either approach.

If I were to make my LAMP web applications available for download and installation by others, should I continue to do table creation in code or follow the example of the other applications I have installed, and just provide a schema file with installation instructions?

x-x
  • 137
  • 7

6 Answers6

4

Most web applications I have installed have a setup "wizard" that runs when I first navigate to where the site is hosted and it will run the scripts using some nice interface, which is a very nice feature.. However, the scripts are all there for me to see usually and i have also bypassed the setup wizard all together and just run the sql scripts myself.

This also brings up the point of database requirements.. is a certain DB required? if so, then you might as well create the installation "wizard" and automatically run the scripts since there is no DB flexibility, however, if i can "choose" which type of persistent storage mechanism to use (Model 1st type design), then it'd be pretty dang nice of you to make an installer which can handle that case (thats probably a product in itself!) but I would NOT expect for an installer to be provided in that scenario. But in that case you're dev'ing for a different audience than the typical "i want to install a blog so I can blog" type of user..

hanzolo
  • 3,111
  • 19
  • 21
2

Are you planning to distribute it, or you're the one who's going to install it? In the former case, go with a setup script (or schema creation on the first run). In the latter case, a schema file would do. The upside of manual schema creation is that you don't have to give the database user that your PHP files are connecting as any schema editing rights. The principle of least privilege and all that.

The upside of automatic schema creation is, obviously, convenience for your users.

Seva Alekseyev
  • 193
  • 10
2

Using scripts to create database used to be the case some time back. It was also the time the database administration is a full time profession. Our application used to ship with oracle sql scripts to manage db creation. Some time DBAs then gave feedback about missing index etc, for performance.

Today we ship with pre-built database (postgres) and customers love it. This also reduced number of support calls related to database administration that we had to support. Number of applications I recently tried also came with transparent databases.

Summary : Yes, database creation should be managed by you application or installer. This is especially true if the target customers are very small companies.

Jayan
  • 511
  • 1
  • 5
  • 14
2

YAGNI applies there.

If it's for your use only, use whatever works best for you.

If it's for distribution, then you need to provide at least instructions on how to use the schema file, or a build script. The easiest it will be, the more people will be interested in going with your application.

Florian Margaine
  • 6,791
  • 3
  • 33
  • 46
1

You should go with a setup script, which the installer can customize.

There is much more to defining a database than you can easily include in the application code. As a system scales up data placement, index options etc. become more important. Its nice to have an install script where you have fine control over all the database parameters, rather than rely on an automatic initialization which picks up all the defaults.

James Anderson
  • 18,049
  • 1
  • 42
  • 72
-1

I'd say it's a matter of preference. Personally, I'd rather build the tables myself, in my code. SQL and Entity Framework support both methods.