Logical Data Model: Design Guide for Data Engineer...

Focus: Business concepts and high-level relationships Audience: Business stakeholders, project sponsors Detail level: Entity names and relationships only. No attributes, no data types, no keys. Example: Customer places Order. Order contains Product. Three boxes with labeled lines between them. Interview relevance: Rarely tested directly. But if an interviewer asks you to start from scratch, beginning with a conceptual model shows methodical thinking.

Focus: Entities, attributes, relationships, and constraints Audience: Data architects, data engineers, analysts Detail level: All attributes listed. Primary keys and foreign keys defined. Cardinality specified. Data types are logical (text, number, date) not platform-specific. Example: Customer (customer_id PK, name, email, city). Order (order_id PK, customer_id FK, order_date, total_amount). One customer to many orders. Interview relevance: Frequently tested. Interviewers ask you to design a logical model for a business scenario. They want to see entities, attributes, keys, and cardinality.

Focus: Platform-specific implementation details Audience: Database administrators, platform engineers Detail level: Column data types are platform-specific (VARCHAR(255), BIGINT, TIMESTAMP WITH TIME ZONE). Indexes defined. Partitioning strategy. Storage parameters. Example: customers (customer_id BIGSERIAL PRIMARY KEY, name VARCHAR(200) NOT NULL, email VARCHAR(255) UNIQUE, city VARCHAR(100)). Partition orders by order_date RANGE. Interview relevance: Sometimes tested in senior roles. The interviewer may ask you to take a logical model and make implementation decisions for a specific platform.

An entity represents a distinct business concept that the organization needs to track. Customer, Order, Product, Employee, Account, Transaction. Each entity becomes a table in the physical model. The entity name should be a singular noun that business stakeholders recognize immediately. If stakeholders do not use the term in daily conversation, it is probably not a real entity. Choosing the right entities is the first and most consequential decision in logical data modeling. Too few entities and you end up with wide tables that mix unrelated concepts (a single 'Person' table for customers, employees, and vendors). Too many entities and you fragment the model into dozens of tiny tables that require excessive joins. A mid-size e-commerce logical model typically has 15 to 25 entities. A financial services model might have 40 to 60. Interview note: When an interviewer asks you to design a logical model, start by listing entities before touching attributes. Say them out loud: 'The core entities are Customer, Order, OrderLineItem, Product, and Payment.' Then pause for feedback. This is the same pattern as declaring the grain in dimensional modeling: establish the big picture before diving into details.

Attributes are the properties of an entity. A Customer entity has name, email, phone, city, state, country, signup_date. An Order entity has order_date, status, total_amount, shipping_address. Attributes in a logical model use logical data types (text, number, date, boolean) rather than platform-specific types (VARCHAR(255), DECIMAL(10,2)). Every attribute should pass the 'single value' test: does this attribute store exactly one value per entity instance? If a customer can have multiple phone numbers, phone_number should not be an attribute on Customer. Instead, create a CustomerPhone entity (or, in a denormalized warehouse model, store the primary phone only). The single-value test prevents the most common logical modeling mistake: cramming multi-valued data into a single column. Interview note: Interviewers pay attention to attribute granularity. Storing 'full_address' as a single text column is weaker than separating it into street, city, state, postal_code, and country. The separated version enables filtering and grouping by geography. If you combine them, an analyst cannot easily answer 'how many customers are in Texas?' without string parsing.

Relationships define how entities connect. A Customer places many Orders (one-to-many). An Order contains many Products and a Product appears in many Orders (many-to-many, resolved through an OrderLineItem junction entity). Relationships have cardinality (one-to-one, one-to-many, many-to-many) and optionality (required or optional). Many-to-many relationships always require a junction (associative) entity in a logical model. Student enrolls in Course becomes Student -> Enrollment -> Course, where Enrollment has student_id and course_id as a composite key, plus its own attributes like enrollment_date and grade. Skipping the junction entity is a modeling error that leads to repeating groups or comma-separated values in a single column. Interview note: Draw relationships with cardinality notation (1:M, M:N) on the whiteboard. If the interviewer gives you a scenario like 'a doctor can see many patients, and a patient can see many doctors,' they are testing whether you create the junction entity (Appointment) with its own attributes (appointment_date, diagnosis, duration_minutes). The junction entity often has the most interesting attributes in the model.

Constraints enforce data integrity rules in the logical model. Primary key constraints ensure every entity instance is uniquely identifiable. Foreign key constraints ensure relationships reference valid entities. Uniqueness constraints prevent duplicates (email must be unique per customer). NOT NULL constraints identify required attributes. Check constraints enforce value ranges (order_total >= 0). In a logical model, constraints are expressed in business terms, not SQL syntax. 'Every order must reference exactly one customer' (foreign key + NOT NULL). 'No two products can share the same SKU' (unique constraint). 'Account balance cannot be negative' (check constraint). These rules document business requirements that the physical model must enforce. Missing constraints in the logical model almost always become data quality bugs in production. Interview note: Mentioning constraints unprompted shows the interviewer you think about data quality at the design stage, not as an afterthought. When listing attributes, note which ones are required (NOT NULL) and which have uniqueness requirements. This takes 10 seconds and signals experience with real production systems where missing constraints cause silent data corruption.