Physical data model
The physical data model represents the process of adding information to the database. This model shows all table structures, including column name, column data type, column constraints, primary key, foreign key, and relationships between tables. Physical data models:
- Specify all tables and columns.
- Include foreign keys to identify relationships between tables.
- May include denormalization, depending on user requirements.
- May be significantly different from the logical data model.
- Will differ depending on which DBMS (database management system) is used.
Build a data model in Lucidchart
Conceptual ERD symbols
These symbols are generally used for conceptual data models, although some aspects may spill over into logical data models. They can be found in the UML Entity Relationship and Entity Relationship shape library of Lucidchart. If you don't see the shape you need, use an image file (Lucidchart supports PNG, JPG, or SVG import) or create your own with our existing shapes and styling options.
Entities
Entities are objects or concepts that represent important data. They are typically nouns, e.g. customer, supervisor, location, or promotion.
- Strong entities exist independently from other entity types. They always possess one or more attributes that uniquely distinguish each occurrence of the entity.
- Weak entities depend on some other entity type. They don't possess unique attributes (also known as a primary key) and have no meaning in the diagram without depending on another entity. This other entity is known as the owner.
- Associative entities are entities that associate the instances of one or more entity types. They also contain attributes that are unique to the relationship between those entity instances.
Relationships
Relationships are meaningful associations between or among entities. They are usually verbs, e.g. assign, associate, or track.
- Relationships provide useful information that could not be discerned with just the entity types.
- Weak relationships, or identifying relationships, are connections that exist between a weak entity type and its owner.
Attributes
- Attributes are characteristics of either an entity, a many-to-many relationship, or a one-to-one relationship.
- Multivalued attributes are those that are capable of taking on more than one value.
- Derived attributes are attributes whose value can be calculated from related attribute values.
Physical ERD symbols
The symbols below are used at the most granular level of ERDsâphysical data models. Some elements are also used for logical data models.
- Tables are another way of representing entities.
- Fields represent attributes of the entity.
- Keys are one way to categorize attributes. A primary key is an attribute or combination of attributes that uniquely identifies one and only one instance of an entity. The primary key becomes a foreign key in any entity type to which it's related through a one-to-one or one-to-many relationship.
- Types may refer to the type of data associated with the corresponding field in a table. Types can also refer to entity types, which describe the structure of an entity (e.g. a book's entity types are author, title, and publish date).
ERD notation
Relationships illustrate an association between two tables. In the physical data model, relationships are represented by stylized lines.
Cardinality and ordinality, respectively, refer to the maximum number of times an instance in one entity can be associated with instances in the related entity, and the minimum number of times an instance in one entity can be associated with an instance in the related entity. Cardinality and ordinality are represented by the styling of a line and its endpoint, as denoted by the chosen notation style.
When it comes to notation, data modelers have many options to choose from. While crow's foot notation is widely accepted as the most intuitive style, some developers use OMT, IDEF, Bachman, or UML notation to indicate cardinality. Since crow's foot notation shows both minimum and maximum cardinality in an easy-to-read graphic format, Lucidchart offers crow's foot notation as the preferred style.
Make an ERD in Lucidchart
Now that youâre all caught up on ERD symbols and notation, try building your own ER diagram in Lucidchart. Itâs free to use and offers every standard entity relationship shape so you can quickly build professional ER diagrams. When youâre done, export your design to MySQL, PostgreSQL, SQL Server, or Oracle to instantly generate a database. For extra credit, take a screenshot of your diagram and share it with us in the comments below (minus proprietary information of course)!
Try Lucidchart free
