Database design involves identifying the existing relationships between separate pieces of data and mapping out those relationships in an organized way that makes sense. There are several types of database design: conceptual database design, logical database design, and physical database design. Logical and physical database design are perhaps the most straightforward. Conceptual database design is a bit more ambiguous because during this phase there is no direct work on a database model. The process is solely an exercise in the identification of relevant data.
Two principal things that are being identified in conceptual database design are entities and relationships — entities being actual objects in the material world, and relationships being the network of connections linking one entity to another indefinitely. Here arises the central notion of this type of design: the entity-relationship model. This does not feature the overall organization and structure that will be inherent in logical database design; it is, however, a precursor to it.
Relationship cardinalities are an essential part of the entity-relationship model used in conceptual database design. Cardinalities express how regularly an entity experiences a particular relationship with another entity. In the actual model these are denoted by the points at which an entity on the diagram branches out to link with single or multiple entities. Various “attributes” such as names, qualities and quantities associated with the entities and relationships are depicted in the model as well.
Final considerations in the development of an entity-relationship model for conceptual database design include assigning each observed attribute to a particular domain and double checking to ensure that everything in the model makes sense. Checking over everything entails finding and filtering out all repeated data, making sure that all attributes are associated with the correct entities and relationships, and confirming that all associations in the diagram are logical. If the connections are not logical in a real world context, they must be logical at least on an abstract level.
Logical database design follows up on the conceptual phase. The process lends order and coherency to those relationships previously mapped and organizes them in such a way that they can actually be used for physical database design. Completion of tasks in physical database design results in a database that is functional and well-structured in light of the work done in conceptual database design and logical database design.