Web Database Applications with PHP, and MySQL

Appendix C. Modeling and Designing Relational Databases

Planning and designing a database is the essential first step to developing a web database application. In this appendix, we introduce database modeling and the techniques to convert a model into the SQL statements needed to create a database.

This appendix isn’t intended to replace a course or book on relational databases. Modeling requirements with an entity-relationship model requires both patience and experience. Instead, we detail our thought processes in a case study that models the winestore requirements and converts these to SQL CREATE TABLE statements. Pointers to resources on modeling and database design are included in Appendix E.

The Relational Model

Relational database management systems, or RDBMSs, maintain, enforce, and use relationships between data. To illustrate the principles of relational databases, we use the winestore system requirements and descriptions from Chapter 1 as the basis for our examples.

Case Study: Relations in the Winestore

There are three essential types of data or entities that form the basis of the winestore. First, there is the wine itself: each wine has characteristics or attributes such as a name, a type, and a variety. Second, there is the customer, who has attributes such as a name, an address, and a phone number. Last, and importantly in selling wine online, is a customer purchase order. It is the order that forms a relationship between customers and wines.

An order is made when a ...