/sql-erd

[sql,erd]

Domain Modeling & ERD

Learning Objectives

  • Draw an Entity Relationship Diagram (ERD) using proper notation
  • Identify and diagram a one-to-one, one-to-many, many-to-many relationship between data entities (physical/virtual)
  • Distinguish between entities & attributes (and when you should use one over the other)
  • Discuss data normalization needs and techniques

Framing

User stories clarify the features we need for a good user experience. We use them to prioritize order and scope. Today, we will identify the information required to support those user stories. We refer to this as the Domain, or Domain Model. The Domain Model specifies the data and the relationships between this data. We use it to decide what needs to be persisted.

Data vs Behavior

  • When problem modeling, we often think of things in terms of nouns and verbs
  • The verbs can be completely captured in code, and since code is saved, they are saved
  • The nouns are represented with placeholders in code (objects, variables, etc)
  • The actual data attached to nouns needs to be persisted! Eg. Game scores, the To-Do list, student list

Domain Modeling

  • Attributes only, not behavior.
  • A domain model in problem solving and software engineering is a conceptual model of all the items/topics related to a specific problem.
  • It describes the various entities, their attributes, roles, and relationships, plus the constraints that govern the problem domain.
  • It does not describe solutions to the problem.

ERDs

  • An ERD is a tool we use to visualize and describe the data relating to the major entities that will exist in out programs.
  • Ultimately lends itself to planning out and creating our database table structure.
  • We keep our data separate from our behavior.
  • There are multiple syntaxes or schemas for diagramming this data. Here's a diagram for an orchard, generated by the rails-erd gem.

erd

  • The squares represent our entities and are filled with the attributes associated with our entity.
  • The arrows between the squares indicate how the entities relate to one another.
    • There are several types of relationships:
      • One-to-many: An Company has many Orchards
      • Many-to-many: An Orchard has many PickingRobots and a PickingRobot works in many Orchards
        • Requires a join table.
      • One-to-one: A User has one Profile
        • Usually denotes that one entity should be an attribute of the other
        • Usually separated for physical space considerations

We Do: Library ERD Example

As a class, let's come up with an example ERD for an application that manages a library.

Exercise: Tunr ERD

In groups of 3, create your own ERD for Tunr. You can keep it as simple or complex as you like.

At a minimum though, you should have Songs and Artists as entities.

Screencasts: