SolutionBase: Configuring a simple database in MySQL 5.0

Each of the entities has attributes listed. The Members entity has names, addresses, telephone numbers, and other contact information. The WorkShift entity records when the member donates time to the co-op by working some number of hours on Co-op business. The Job entity describes the type of work performed.

The relationships among entities are represented by lines on the model, connecting entities and providing text descriptions of the relationship. To read a relationship, start with one entity and travel to the next, following the line and text descriptions. For example, starting with Member, we see that each member may (dashed line) work one or more (multiple line) WorkShifts. You can also read this backwards: each WorkShift must (solid line) be the record of time worked by one and only one (single line) Member.

Derive a physical model

Before we can implement the Co-op's logical model, however, we have to refine it into a physical one. Logical models are useful for understanding the structure of the proposed database, but don't contain enough information to actually build it. A physical model targets a specific database product: the same logical model might be implemented differently on Microsoft SQL Server, MySQL, or Oracle due to differences in the data types and other features available. It's important to do this kind of targeting instead of staying generic, because if you don't take advantage of the specific features in the database, the result will not perform as well as it could.

The first task in refining the model is to decide which MySQL datatypes will be used to represent each attribute. We'll also need to identify which attributes are required versus optional, and which uniquely identify each entity. We'll need all of this detail to create actual tables in the new database.

MySQL 5.0 follows the ANSI SQL 2003 standard very closely, so the datatypes it supports are generally available in MySQL. It has five different sizes of integers, for example, from TINYINT (1 byte, maximum value 255) to BIGINT (8 bytes, maximum value 18446744073709551615). Fixed length character fields, CHAR, are still limited to 255 characters, but variable length character fields, VARCHAR, can now go up to 65,532 characters. There are a variety of date and time data types as well, such as DATE and TIME that store their data separately, or DATETIME and TIMESTAMP that store them together in a single column.

One common problem in translating a logical model to a physical one is the presence of Many-to-Many relationships, which are not supported directly by relational databases. Our diagram shows one such relationship between Member and Job: a given Member may be able to perform several different jobs, and conversely, a given Job may be able to be done by multiple members.

Such Many-to-Many relationships must be decomposed in the physical model by creating a new entity as a "junction" between them. This changes the Many-to-Many relationship into a pair of One-to-Many relationships, which is easy for relational databases like MySQL (or SQL Server, or Oracle) to deal with. We've added an entity called Training to accomplish this.

Also, the physical model is the place to show any changes you make purely for performance reasons. One such "denormalization" introduces redundant summary data to avoid having to read all the detail. For example, to determine the member's status for the recognition program, we could read through all their work shift information and apply a formula. But by putting a redundant Karma attribute in the Member entity, we only have to look one place to find their current status.

Of course, we'll have to keep that information up to date. A stored procedure could be run on a regular basis to assign member Karma, or a trigger could be used to keep it up to date in real time as WorkShift records are entered.

Above shows the physical model with specific data types, the junction table Training, and the redundant Karma attribute in Member.