2.1Conceptual design and E-R diagrams

The first task facing a database designer is conceptual design: capturing the objects a business deals with (customers, orders, products, and so on) without omission, and organizing the relationships among them. If this modeling is done carelessly, no amount of correct normalization or SQL implementation downstream can produce a schema that actually represents the business requirements. This section covers the fundamentals of conceptual design via the E-R diagram and the design judgment of how to translate a many-to-many relationship—something a relational database cannot represent directly—into a relational schema.

2.1.1Entities, relationships, and attributes

• An entity is an object the business wants to identify and manage separately (a customer, a product, an order, and so on). An entity typically corresponds to one table in a relational database. The properties an entity has are expressed as attributes (a customer's name, address, and so on), and the set of attributes that uniquely identifies an entity becomes the basis of a candidate key.
• A relationship is a connection between entities (e.g., "a customer places an order," "an order contains a product"). A relationship can itself carry attributes (for example, the "quantity" attribute on the "an order contains a product" relationship belongs to neither the customer nor the product—it is a property of the relationship itself).

2.1.2Cardinality

• Cardinality expresses the correspondence in the number of instances of entities participating in a relationship. It is expressed as one of three kinds: one-to-one (one employee uses one company PC), one-to-many (one customer places multiple orders), or many-to-many (one order contains multiple products, and one product appears in multiple orders).
• Getting cardinality wrong later causes mistakes in where to place a foreign key in the relational schema, or even in how tables should be split in the first place. The practical rule of thumb is to confirm, through business interviews, how many instances on the other side a single instance can actually have, before finalizing the cardinality.

2.1.3Resolving many-to-many relationships

• A single relational table can directly express only up to a one-to-many relationship via a foreign key. A many-to-many relationship is resolved by introducing a new "associative entity" (a junction table) that holds both entities' primary keys as foreign keys, decomposing the many-to-many relationship into two one-to-many relationships.
• The associative entity can also hold attributes that belong to the relationship itself (for an "order line," quantity, unit price, and so on). The associative entity's primary key is typically the composite of both foreign keys, but if that same combination can legitimately repeat in the business (e.g., ordering the same product multiple times in the same order under different conditions), adding a dedicated surrogate key is also a valid design choice.

A database designer at an e-commerce company is modeling the relationship between "orders" and "products" in an E-R diagram. Interviews reveal that one order can contain multiple products, and one product can appear in multiple orders, so the designer judges this to be a many-to-many cardinality. Suppose the designer mistakenly proposes adding several columns to the "order" table (product ID 1, product ID 2, product ID 3, and so on) to hold the product IDs. This design forces an upper bound on the number of products per order to be fixed in advance, and requires a schema change (adding a column) the moment an order exceeding that bound arrives—a breeding ground for update anomalies. The correct resolution is to introduce a new associative entity called "order line," holding both the order ID and the product ID as foreign keys, and to store the attributes that belong to the relationship itself—"quantity" and "unit price at time of sale"—in this order-line table. The order-line table's primary key is normally the composite (order ID, product ID), but if a business requirement then surfaces that "the same order can order the same product multiple times with different requested delivery dates," (order ID, product ID) alone can no longer uniquely identify a row, and the design must switch to adding a dedicated surrogate key, an order-line ID. Determining cardinality and designing the associative entity's primary key are therefore judgments that can only be finalized by digging into the details of the business rules—specifically, whether the same combination is allowed to repeat.

2.1.4Section summary

• An E-R diagram models the business by treating entities as tables, attributes as columns, and relationships as correspondences between tables
• Cardinality (one-to-one / one-to-many / many-to-many) must be confirmed through business interviews, since it governs downstream foreign-key design
• A many-to-many relationship is resolved by introducing an associative entity (junction table) that decomposes it into two one-to-many relationships; the junction table's primary-key design depends on the business rules