Creating and Managing Lookup Tables in ER Diagrams

This tutorial provides a comprehensive guide on creating and managing lookup tables in our powerful tool for database diagramming. This guide will walk you through the process of enhancing an example database schema by adding and configuring a new lookup entity, ensuring effective data management and visualization within your diagrams.

Step 1: Setting Up the Major Entity

  • Open the example Enrollment project. You can download it here: Enrollment (ER Diagram)
  • Create a new entity:
    • Click on the toolbox and then on the diagram canvas to add a new entity.
  • Name this entity 'Major'.
  • Add two attributes to the Major entity:
    • 'ID' - and set it as a primary key (right-click on the attribute and choose Primary Key from the context menu)
    • 'Name' and choose varchar type (right-click on the attribute and choose Change Type and varchar from the context menu)

Step 2: Modifying the Student Entity

  • Change Major attribute to MajorID:
    • In the 'Student' entity, rename the 'Major' attribute to 'MajorID'.
    • This change creates a more explicit relationship between the 'Student' and 'Major' entities.

Step 3: Connecting Entities and Handling Data Types

  • Create a relationship:
    • Choose the '1 to 1..n' relationship from the toolbox
    • Draw a relationship line from the 'Student' entity to the 'Major' entity.
  • Resolve data type mismatches:
    • If prompted by a warning about differing data types between 'ID' and 'MajorID', choose the appropriate option (int) to resolve this.

Step 4: Populating the Major Lookup Table

  • Add data to Major entity:
    • Select the 'Major' entity.
    • Click on the 'Add Data Row' button in the context bar.
    • Enter values such as 'Computer Science', 'Biology', 'History', and 'Psychology' with appropriate IDs (e.g. 1,2,3)

Step 5: Managing Data Visibility

  • Hide data in the ER diagram:
    • Right-click on the 'Major' entity.
    • Navigate to 'Show Parts' in the context menu.
    • Uncheck 'Data' to hide data from being displayed in the diagram.

New Comment