Find entities, attributes and keys
Part 1 of 1
Start from the client’s words
A waterfront rental business records customers, kayaks and bookings. A customer has a name, an email and an optional phone number. A booking belongs to one customer and has a booking date.
An entity has facts of its own. An attribute describes an entity. Split a composite name if the business needs its parts. Put multiple values or repeated events in their own records. Keep a derived amount calculable from its inputs.
Use a surrogate id as the primary key when following the course convention. Keep a required natural identifier such as a unique email NOT NULL UNIQUE when the requirements establish that rule.
Worked example
Entities: customer, kayak, booking. The list of kayaks represents multiple related facts and belongs in booking_items, one row per booked kayak.
Pause and explain. Give one natural identifier, one surrogate identifier and a requirement you would ask the client to clarify.