Logical schemas were quite popular back in the days of Database Engineers. We studied database storage techniques and data modeling. We learned tools allowing us to efficiently create diagrams of our database schema, the purpose of which was to communicate to the world what our intentions were. Today, database engines are so efficient; some software is developed with no centralized understanding of the data model. In fact, diagrams are often forbidden as a waste of time…just write the code. Many are the student graduating with a degree in information systems that has never seen or created a database diagram.
There is a lot more a Database Diagram can show you about your schema than simply tables, columns and relationships. More often, this was expressed in a Logical Database Diagram. A logical database diagram can actually work more like an Object Oriented data structure. Here is a good example.
In a physical database you may have a table called person. The person table may have a person type to help you understand what the purpose of this person record may be. If related to an Employee table, a person record’s purpose is even further clarified.
In a logical database diagram this distinction is not required to understand the purpose of a person. You can create sub-types in a logical database diagram. So, from a person table, you might subtype to different kinds of persons. You might have employee, customer, sales representative, spouse, dependent, board member, and more.
Logical diagrams will sometimes contain additional attributes (columns), or may simply be maintained at the entity level. Logical diagrams do not use Engine Specific data types. They use generic data types that may be converted into the physical data types of a targeted engine. Good diagramming software, supporting logical models, include conversion utilities to convert a logical diagram into a physical diagram.
Have you used logical data models effectively? Share your experience in our comments, good or bad.
Cheers,
Ben