4 Entities and attributes

This chapter moves from requirements gathering into the design and analysis phase, focusing on data modeling to produce an entity-relationship diagram for The Sci‑Fi Collective’s online store. It explains how to turn subjects and their characteristics into entities and attributes, emphasizing clear, consistent naming. The chapter recommends singular names for entities and a consistent attribute style (such as snake_case), and advises checking for overly long identifiers and avoiding reserved SQL keywords by renaming them when necessary (for example, replacing a word like “order” with “purchase”). The result is a coherent set of entities and attributes ready for the next stages of database design.

The chapter then concentrates on keys—defining superkeys, candidate keys, and primary keys—and offers a practical method to identify and choose among candidate keys using criteria such as uniqueness, non-nullability, stability, simplicity, familiarity, length, and performance. Through concrete examples, it shows how email is selected as the user table’s primary key over username or phone number due to stability, and why a product_code is preferred to a composite of name and manufacturer. When no satisfactory natural key exists, the chapter advocates introducing a surrogate key (for example, auto-increment IDs like review_id, payment_id, and purchase_id), noting that in some tables controlled redundancy is acceptable or even expected while uniqueness of row identity remains paramount.

Finally, the chapter establishes data types for all attributes. It outlines guidelines for string types (CHAR for fixed-length, VARCHAR for variable but bounded length, TEXT for large free text) and cautions about performance and length declarations, as well as the importance of character sets. It covers integer types of different sizes and the signed/unsigned choice, and explains why seemingly numeric values such as card_number, expiry_date, and UPC-like product codes should be stored as strings to preserve leading zeros. For monetary values, it recommends fixed-point types (DECIMAL with appropriate precision and scale) over floating-point to avoid rounding errors. It concludes with temporal types (DATE, TIME, DATETIME, TIMESTAMP), recommending TIMESTAMP for cross-time-zone events and storing times in UTC for consistency, while reminding readers that specific data type names and behaviors can vary across RDBMS and may require small adjustments.

• Design of an entity requires identification of all its attributes, the primary key, and data types of all its attributes.
• A primary key should be able to identify each row uniquely in a table. Depending on your case, you may need a primary key to be able to prevent redundancies. If there are no candidate keys available, or none of the candidate keys are a good fit for the primary key, you can always create a numeric attribute and use it as the primary key.
• Some common data types in databases include string types, integer types, decimal types, and temporal types. When it comes to deciding which data type to use, you need to think about what job a data type is good for as well as the demand of your particular case.
• Given an attribute, to identify its data type, you need to examine the sample data and the gathered information from the requirements gathering phase, identify the proper data type group (e.g., string), and choose the best fit within that group (e.g., TEXT) based on the maximum allowed values or spaces derived from your requirements analysis.
• The use of proper data types ensures that a database stores data efficiently as well as meets the demands of data querying.

FAQ