Diagramming Guidelines
for
Data Model and Physical Database Design (ERD)

• Conceptual Data Models include ...
  • Entities
  • Attributes
    • Only Identifier (Identifying attribute) is needed
  • Relationships (including M:N)

• Logical Data Models include ...
  • Entities
  • Attributes
    • Identifier (Identifying attribute)
    • other appropriate attribute(s)
    • but NO Foreign Key attributes
  • Relationships (including M:N)

• Physical Data Models (ERDs) include ...
  • Tables
  • Table attributes
    • Include all needed fields: PK(s), FK(s), and other fields.
    • Specify data type and data size of each field.
    • Are Null values allowed in our SCA table attributes? Unless otherwise stated ...
      • PK attributes always do not "Allow Nulls"
      • FK attributes do not "Allow Nulls" in most situations
      • All other attributes do "Allow Nulls"
      Note: "NN" unchecked (in the MySQL Workbench GUI) is the same as "Allow Nulls" checked (in the SQL Server GUI)
  • Relationships
    • Note: Convert any M:N relationship (in the logical data model) to an intersection table (in the physical data model).
    • Note: An associative entity (show the N:M rlationship of two tables, plus at least one extra attribute for a characteristic that is not in either related tables) is an entity treated as other entities (in the logical data model), which will be converted to an associative table (in the physical design).
• Selecting the Identifier in the Logical Data Model and Primary Key in the Physical Data Model:
  
  Often, even when there is a good candidate key, a database designer may choose not to use that candidate key as identifier in the Logical Data Model or the primary key in the Physical Data Model. Instead, the designer will add an identity field (auto-increment) for the identifier or primary key. This practice is very common because identity fields make it easier to manage the PKs and FKs in Database Management Systems. Also, the amount of CPU time/power used to run complex queries may be less with identity fields than with candidate keys. (Processing keys with "string" data types can be especially slow.)
  In MIS 421, use an identity field for the identifier or primary key, except
  ... when the identifier or primary key is a composite
  ... when the entity or table is a subtype entity or table
  ... when your instructors direct you not to use an identity field.