Relational Database Design - Page 8

Review the Design Goals
When we started, we said:

"The database will hold portfolio works for my web design portfolio. The database will hold information about each work such as title, category, technology used, and client name."

At this point, the database does hold all of this information. All of the information can be extracted just using SQL, without the use of any server-side scripting languages such as PHP. The ability to manage the data without using a server-side language improves coding speed, processing speed, and ease of maintenance.

The Final Product
Your database should now have the following structure. Ideally, you have also been creating the database entity relationship diagram to match your database design.

By normalizing our database, we have made each table and ultimately the entire database easier to understand. Each data table should make sense on its own. Each linking (mapping) table should bring together two or more data tables. Ultimately, our new simplified structure allows us to better manage our data. While we may need to invest more time in learning SQL queries like joins, our productivity will improve because we will resolve problems via SQL, before they collide with a server-side language.

Parting Words of Advice
Creating database entity relationship diagrams is your best friend and protector. You will thank yourself later when the diagram saves you from a grave mistake. Diagrams can also usually be partially reused for other sites because the tables are very modular - that is, they can be removed from context and still make sense. In addition, they will act as a great reference when you are writing SQL queries. Diagrams don't need to show the data type, but at the very least they should correctly identify table names, column names, primary keys, foreign keys, and most importantly the relationships between the tables.

Download the SQL
Complete SQL Queries for the Portfolio Database (open in text editor like Notepad or Dreamweaver).

If you have any questions, feel free to post on the forums.

Brian Haveri aka bwh2 brianhaveri.com

1 | 2 | 3 | 4 | 5 | 6 | 7 | 8