Achieving minimal maintenance on a web site with a database backend
May 6, 2010 12:55 PM Subscribe
What are some good practices for making a website with a database backend easy to maintain?
My amateur hockey league's current "database" for game and player statistics is a pile of HTML documents that must be tediously updated by hand. I've volunteered/been asked to create a database for them and update the league website so that it can access the database and calculate statistics.
However, the upside to a pile of HTML documents is that almost everyone in my league understands how to update them, even if it takes a long time. The downside, of course, is that it is a mess--scores can be incorrectly copied, statistical information needs to be calculated by hand, it's difficult to change the look of the site, etc.
Given that:
-The database grows very slowly (about 150 games a year, and maybe 10 new players, so maybe two thousand rows of new information)
-Most of the data doesn't change once it's entered
-I'm going to use the MVC architecture to make it easier to update the look of the website without touching the functionality
-I'm using PHP/MySQL/JQuery because I'm most familiar with these languages and because it's more likely that people will be familiar with them
-I will be documenting my code to death
-The league does not have the funds to hire a full time developer
-The league does have a few programmers in it that are not versed in web development
My specific questions are:
-What are some other things that I could do that will make it easier for the league to take care of the website once I have left?
-Should I be using other languages/architectures?
-What are good sites or books that cover the security aspects of web development? I have some understanding of it but would prefer to have a broad overview to make sure I haven't missed anything. The database will not contain much personal information, but making sure it doesn't get randomly hacked would make everyone's life easier
-Is there anything else I should keep in mind while designing the site?
My amateur hockey league's current "database" for game and player statistics is a pile of HTML documents that must be tediously updated by hand. I've volunteered/been asked to create a database for them and update the league website so that it can access the database and calculate statistics.
However, the upside to a pile of HTML documents is that almost everyone in my league understands how to update them, even if it takes a long time. The downside, of course, is that it is a mess--scores can be incorrectly copied, statistical information needs to be calculated by hand, it's difficult to change the look of the site, etc.
Given that:
-The database grows very slowly (about 150 games a year, and maybe 10 new players, so maybe two thousand rows of new information)
-Most of the data doesn't change once it's entered
-I'm going to use the MVC architecture to make it easier to update the look of the website without touching the functionality
-I'm using PHP/MySQL/JQuery because I'm most familiar with these languages and because it's more likely that people will be familiar with them
-I will be documenting my code to death
-The league does not have the funds to hire a full time developer
-The league does have a few programmers in it that are not versed in web development
My specific questions are:
-What are some other things that I could do that will make it easier for the league to take care of the website once I have left?
-Should I be using other languages/architectures?
-What are good sites or books that cover the security aspects of web development? I have some understanding of it but would prefer to have a broad overview to make sure I haven't missed anything. The database will not contain much personal information, but making sure it doesn't get randomly hacked would make everyone's life easier
-Is there anything else I should keep in mind while designing the site?
What are some other things that I could do that will make it easier for the league to take care of the website once I have left?
Try to avoid rolling your own solutions. It will be much easier for the people following you to get help and support if you adapt some existing system, rather than if you develop everything from scratch.
A PHP CMS like drupal or Joomla will do 90% of the work for you, like generating page templates or handling user authentication, freeing you up to focus on the 10% that is actually different in your case and hasn't been already done ten times over by other people.
Using a proven platform as a base is also a good idea from a security standpoint, writing boilerplate security stuff like login screens and doing it right is actually Real Hard.
posted by Dr Dracator at 1:08 PM on May 6, 2010
Try to avoid rolling your own solutions. It will be much easier for the people following you to get help and support if you adapt some existing system, rather than if you develop everything from scratch.
A PHP CMS like drupal or Joomla will do 90% of the work for you, like generating page templates or handling user authentication, freeing you up to focus on the 10% that is actually different in your case and hasn't been already done ten times over by other people.
Using a proven platform as a base is also a good idea from a security standpoint, writing boilerplate security stuff like login screens and doing it right is actually Real Hard.
posted by Dr Dracator at 1:08 PM on May 6, 2010
Best answer: 1) What are some of the things you could do to make it easier ...?
A) Comment your code.
B) Write up a document which describes the interaction of the database with your various scripts, relevant Apache configuration files, filesystem permissions, and any other "big picture" items.
C) Write up a document (or include in the previous) with the common failure modes listed. ("Oh, yeah, if you see this, that means that the logs are full.")
3) Hacking Web Applications Exposed is nice. I can hardly give an exhaustive list, but allow me to make two suggestions:
A) Place a file containing whatever usernames and passwords you would use inside of your scripts in a file location outside of the Apache root, then have your scripts access and read that file. This assures that, if your webserver, for whatever reason, begins to just print PHP right to the screen instead of executing it, this information will not be revealed. Putting that information outside of the Apache root isn't perfect, but it makes it not-easily-browseable.
B) SQL injection is a biggie. Do not make SQL statements to be executed by concatenating strings together. No matter how nicely you sanitize and whitelist your data, you are better you are smarter than someone else. And there's always someone smarter. So, use parameterized queries. Basically, you have a little SQL statement with holes in it for your parameters to go. Your database API will then carefully escape your parameters for you and then drop them into the holes.
4) Anything else to keep in mind ...?
A) Use clean CSS. Validate your HTML and CSS.
B) Test everywhere. Test using Lynx. Test using a friend's iPhone.
posted by adipocere at 1:18 PM on May 6, 2010
A) Comment your code.
B) Write up a document which describes the interaction of the database with your various scripts, relevant Apache configuration files, filesystem permissions, and any other "big picture" items.
C) Write up a document (or include in the previous) with the common failure modes listed. ("Oh, yeah, if you see this, that means that the logs are full.")
3) Hacking Web Applications Exposed is nice. I can hardly give an exhaustive list, but allow me to make two suggestions:
A) Place a file containing whatever usernames and passwords you would use inside of your scripts in a file location outside of the Apache root, then have your scripts access and read that file. This assures that, if your webserver, for whatever reason, begins to just print PHP right to the screen instead of executing it, this information will not be revealed. Putting that information outside of the Apache root isn't perfect, but it makes it not-easily-browseable.
B) SQL injection is a biggie. Do not make SQL statements to be executed by concatenating strings together. No matter how nicely you sanitize and whitelist your data, you are better you are smarter than someone else. And there's always someone smarter. So, use parameterized queries. Basically, you have a little SQL statement with holes in it for your parameters to go. Your database API will then carefully escape your parameters for you and then drop them into the holes.
4) Anything else to keep in mind ...?
A) Use clean CSS. Validate your HTML and CSS.
B) Test everywhere. Test using Lynx. Test using a friend's iPhone.
posted by adipocere at 1:18 PM on May 6, 2010
Best answer: I'd recommend using an established framework and take it from there. The couple I've looked into even seem to have a pre-built module to handle the kind of thing that you're doing.
Drupal and Sports Club Management module
Joomla and Beer League extension
If you have a bit of dev time you might even be able to help contribute to the project and give back to the FOSS community.
posted by talkingmuffin at 2:16 PM on May 6, 2010
Drupal and Sports Club Management module
Joomla and Beer League extension
If you have a bit of dev time you might even be able to help contribute to the project and give back to the FOSS community.
posted by talkingmuffin at 2:16 PM on May 6, 2010
Use expressionengine in combination with the matrix plugin for the scores, anything you have to build in addition to what EE and matrix can do, create as a plugin or extension,
http://expressionengine.com/
http://pixelandtonic.com/matrix
Do NOT roll your own, when you leave they will have a custom system that will cost way too much to update/keep and will start all over again.
posted by madmanz123 at 2:17 PM on May 6, 2010 [1 favorite]
http://expressionengine.com/
http://pixelandtonic.com/matrix
Do NOT roll your own, when you leave they will have a custom system that will cost way too much to update/keep and will start all over again.
posted by madmanz123 at 2:17 PM on May 6, 2010 [1 favorite]
It would be really easy to do this in Django, particularly if you leverage their built-in admin section, to allow your admins to enter data.
Level of Difficulty:
Know python: easy
Don't know python: moderate
posted by i_am_a_Jedi at 2:43 PM on May 6, 2010
Level of Difficulty:
Know python: easy
Don't know python: moderate
posted by i_am_a_Jedi at 2:43 PM on May 6, 2010
Best answer: Rate the systems suggested by how little code you need to write in order to get the functionality you need. Ideally one of the CMSes above will require only a few template tweaks and MySQL tables created to do what you want.
posted by benzenedream at 2:56 PM on May 6, 2010
posted by benzenedream at 2:56 PM on May 6, 2010
Response by poster: I glanced at CMS options early on in the design, but dismissed it as being too complicated for such a tiny site and forgot to come back to it. Your answers have convinced me that it's the way to go, especially when there are extensions for sports leagues. Thanks so much, this has taken a big weight off my shoulders.
I'd love to hear more suggestions for content management systems, if people have any, as well as general maintenance advice. The suggestion to clearly document the meanings of the failure modes is great, for instance.
posted by millions of peaches at 2:57 PM on May 6, 2010
I'd love to hear more suggestions for content management systems, if people have any, as well as general maintenance advice. The suggestion to clearly document the meanings of the failure modes is great, for instance.
posted by millions of peaches at 2:57 PM on May 6, 2010
Use sensible variable names! PHP won't care if you call things $x1, $x2, $a and $tv, but the next techie to maintain it will thank you for not using names like that.
And I imagine this goes without saying, but design your database correctly - don't have one gigantic table with duplicate information in records. Use primary/foreign keys and sketch out relationships between tables before coding.
posted by Xany at 8:32 PM on May 6, 2010
And I imagine this goes without saying, but design your database correctly - don't have one gigantic table with duplicate information in records. Use primary/foreign keys and sketch out relationships between tables before coding.
posted by Xany at 8:32 PM on May 6, 2010
One other aside: try to calculate stats inside the database (using views) when possible to minimize custom code.
posted by benzenedream at 1:35 AM on May 7, 2010
posted by benzenedream at 1:35 AM on May 7, 2010
This thread is closed to new comments.
In fact, get someone else involved in maintaining it ASAP, once you have something that basically runs.
PHP is a good language for this. I say this despite not personally liking PHP. If you want something that "anyone" (with a few skills) can take and add onto, install on different web hosting, etc., PHP is good for that.
Limit scope as much as possible. Is this a database plus a bunch of articles? Put the articles on a pre-canned blog platform and develop as little as possible from scratch (i.e. just your custom stats database).
posted by pengale at 1:00 PM on May 6, 2010