CMS and E-commerce

Database and Programming Design

Continuing our last articles about Basic development design, lets have a look at database design and programming design in this article.

Designing the database is a key step, largely because changes to the database at a later date have far larger implications and potential complications than changing any other aspect of the site. Adding functionality through database changes is a steep challenge and fixing database flaws is excruciating, so make every effort you can to get the database design right the first time.

Good database design begins, naturally, with normalizing the database. If you aren’t familiar with normalization, see any good resource on the subject. Normalization and performance mean that you also:
1. Use the smallest possible column types.
2. Avoid storing NULL values as much as possible.
3. Use fixed-length columns when you can.
4. Provide default values for columns, if applicable.

Performance is also greatly affected by using indexes properly. Declaring indexes is somewhat of an art, but some general rules are:
1. Index columns that will be involved in WHERE and ORDER BY clauses.
2. Avoid indexing columns that allow NULL values.
3. Apply length restrictions to indexes on variable-length columns, such as
indexing only the first 10 characters of a person’s last name.
4. Use EXPLAIN queries to confirm that indexes are being used.
5. Revisit your indexes after some period of site activity to ensure they are still
appropriate to the real-world data.

A final consideration in your database design, which gets less attention, is the storage engine (or table type) in use. One of MySQL’s strengths is its support for multiple storage engines, meaning you can select the one whose features best match your needs.

Okay now lets talk about programming design, from a programming perspective, you’ll want to create code that’s not only functional, but also reusable, extendable, and secure.

To make reusable, extendable code, it must be well organized and thoroughly documented. I cannot stress this enough: Document your code to the point of overkill. As you program, begin with your comments and revisit them frequently. When you make any changes to your code, double-check that the comments remain accurate. You should also use flowcharts, UML diagrams (Unified Modeling Language), and other tools to outline and represent your site in graphical and noncode ways.

The security of your code is based upon so many factors that the next chapter will start discussing just this one subject. Secure programming is even more critical in e-commerce sites, however, so the topic will be reinforced time.