Relational Database Design - Page 2

Designing Our First Table: tbl_works
When designing a database, the easiest table to create is usually derived from the core functionality. In this case, we are building a database to house web design works. So the first table we create is going to hold the works: tbl_works. The works table will hold five columns: work_id, work_title, work_dscpn, work_date, work_image_url. The work_id column is set as a Primary Key. Primary Key fields, often marked "PK" are a way to uniquely identify a row in a table. This means that a work_id number will never be duplicated within tbl_works. Each work will have one and only one work_id.

In addition, our work_id column is also set to auto increment. By setting the column work_id to auto increment, we tell the database to automatically generate a new work_id (previous work_id+1) for each record added to the table. Typically, ID fields are set to data type integer (INT) because integers are quickly and easily processed.

The diagram shown above is an Entity Relationship Diagram. Database ERDs are used to show relationships between tables in database as well as table structures. This diagram was created using Microsoft Visio, but any drawing tool (even pencil and paper) will work. Because ERDs help visualize table structures and relationships, they often lead us to better database designs.

SQL Tip

While storing images in a MySQL database is possible, it is not advisable for speed and stability reasons. Instead, store the URL to the image as shown.

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