Data models for analysts

Why it matters

Processes and use cases describe what happens. A data model describes what the system must remember. If the model is wrong, every screen and report built on it is wrong too, and fixing a data model late is expensive. Analysts do not usually design the final database, but they sketch a conceptual or logical model from the requirements so the business can check it and the developers start from the right shape.

The three building blocks

• Entity: a thing the business needs to keep information about. Usually a noun in the requirements: Member, Room, Booking.
• Attribute: a fact about an entity: a room's name and capacity, a booking's date and start time.
• Relationship: how entities connect: a member makes bookings; a booking is for a room.

Each relationship has a cardinality: how many of one can relate to how many of the other.

From requirements to entities

Read the requirements and underline the nouns, then ask which ones the system must store facts about.

"A member can book a room for a date and time of up to two hours. Each room has a capacity. Librarians can cancel a booking and record a reason."

• Member, Room, Booking are entities.
• Date, start time, end time, status and cancellation reason are attributes of Booking.
• Capacity is an attribute of Room.
• "Booking" is the interesting one: it is both a verb in the story and an entity in the model. Events that the business needs to remember (bookings, orders, payments, loans) almost always become entities.

Drawing the ERD

An entity relationship diagram (ERD) draws entities as boxes and relationships as lines. The most common style today is crow's foot notation: a "crow's foot" (three prongs) at the "many" end of a line, a single bar at the "one" end.

MEMBER                 BOOKING                   ROOM
---------              ---------------           ----------
id (PK)   ||------<|   id (PK)        |>------|| id (PK)
name                   member_id (FK)            name
email                  room_id (FK)              capacity
                       booking_date
                       start_time
                       end_time
                       status

Read it aloud: "one member makes many bookings; one room has many bookings". The primary key (PK) uniquely identifies each row. The foreign keys (FK) in Booking point to the member and the room.

Many-to-many becomes a bridge

A room can have many features, and a feature can be in many rooms. Relational databases cannot store M:N directly, so the model adds a bridge (junction) table:

ROOM ||------<| ROOM_FEATURE |>------|| FEATURE
               room_id (FK)
               feature_id (FK)

Test the model against the requirements

A model is only right if it can answer the questions the business will ask. Take each report or screen in the requirements and check that the data is there:

• "Show availability for a day" needs bookings with room, date and times. Yes.
• "How many confirmed bookings did each room have last week, including rooms with none?" needs status and date on Booking, and every room. Yes.
• "Which groups have accessibility needs?" There is nothing for that. Either the requirement is out of scope or the model is missing an attribute. Raise it.

This is where SQL helps an analyst. A quick query against sample data shows whether the model really answers the question, before anyone builds screens. Note the trap in the second question: an inner join drops rooms with no bookings, so you need a LEFT JOIN and must put the date and status filters in the ON clause rather than in WHERE.

Common mistakes

• Storing a list in one field ("features: projector, whiteboard") instead of a related table.
• Missing the event entity: modelling Member and Room but no Booking.
• Duplicated facts: the room name copied onto every booking, which drifts out of sync.
• Ignoring history: overwriting a booking's status when the business needs to know when and why it was cancelled.
• Never testing the model against the actual reports people want.

Quick recap

• Entities are things to remember, attributes are facts about them, relationships connect them.
• Find entities from nouns and from events the business must remember.
• Crow's foot ERDs show cardinality; M:N relationships need a bridge table.
• Test the model by checking it can answer every required question; SQL makes that concrete.

Try it

Open the Do tab. The library's model is already built as three tables. Write the query that checks it can answer the sponsor's weekly report.