Data Modeling experimental

Data Modeling The shape of data before the query: entities, relationships, keys, normalisation, and choosing relational, document or graph. Entities and relationships. Entities and relationships. Draw an ER model of a domain.. Distinguish entities, relationships, and cardinality when reading a domain description.. Entities, Relationships, Cardinality, ER diagrams Entities and Relationships Before a single table is created or a single query is written, data modeling starts with a question: what are the *things* this system needs to remember, and how do those things connect? Get this shape right and everything downstream (schemas, indexes, queries, migrations) gets easier. Get it wrong and you will spend years fighting the model. Entities An entity is a distinct, identifiable thing the business cares about: a Customer, an Order, a Product, a Warehouse. An entity is not a table and not a class: it is a concept. Each entity has attributes that describe it (a Customer has a name, an email, a signup date) and, crucially, some way to tell one instance apart from another. If two things in your domain can never be told apart by the business, they are probably the same entity, not two. A common early mistake is confusing an entity with an attribute. "Address" feels like a thing, but if a customer only ever has one address stored as a few fields, it may just be attributes of Customer. If a customer can have many addresses (billing, shipping, historical), Address becomes its own entity with a relationship back to Customer. Relationships A relationship is an association between entities: a Customer *places* an Order; an Order *contains* Product line items. Relationships carry meaning: the verb matters. Some relationships carry their own attributes (an order-product association has a quantity and a price-at-time-of-sale), which is a signal that the relationship itself deserves to become an entity (often called an associative or junction entity). Cardinality Cardinality describes how many instances of one entity can be associated with how many instances of another: one-to-one (1:1), one-to-many (1:N), or many-to-many (M:N). A Customer places many Orders (1:N). A Student enrolls in many Courses, and each Course has many Students (M:N); this always requires a junction entity to represent it in a relational design. Getting cardinality wrong is one of the most expensive modeling mistakes: modeling a relationship as 1:N when it is really M:N forces a painful migration later. Always ask: "can there be more than one, in either direction, ever?", including in edge cases the business hasn't hit yet but will. Cardinality is also paired with optionality (or participation): must every Order have a Customer, or can orders exist without one (e.g., guest checkout)? This is usually drawn as (min, max) pairs, such as (0,1) or (1,N). ER Diagrams An entity-relationship (ER) diagram is the visual contract for a domain: boxes for entities, lines for relationships, and notation (crow's foot is most common) for cardinality and optionality at each end of the line. A good ER diagram is readable by a non-technical stakeholder and answers disputes before code is written, since it is cheaper to argue over a diagram than over a migration. Practice reading crow's-foot notation until it is second nature: a circle means optional (zero), a single tick means exactly one, and a crow's foot means many. Related CCI capabilities Computer Architecture (Course): (https://www.cambridgecyberinternational.com/en/insights/academy/computer-architecture/). Optics Primer Series (Course): (https://www.cambridgecyberinternational.com/en/insights/academy/optics/). Maths Refresher Series, Finance (Course): (https://www.cambridgecyberinternational.com/en/insights/academy/maths-finance/). System Dynamics (Course): (https://www.cambridgecyberinternational.com/en/insights/academy/system-dynamics/). CCI Lab: Run it, build with it, read the thinking, reuse the data. (https://www.cambridgecyberinternational.com/en/insights/lab/) Keys and normalisation. Keys and normalisation. Derive a normalised schema to remove update, insertion, and deletion anomalies.. Distinguish partial, transitive, and full functional dependencies across the normal forms from 1NF to BCNF.. Keys, Functional dependencies, 1NF to BCNF, Denormalisation trade-offs Keys and Normalisation Normalisation is the discipline of organising columns into tables so that each fact is stored exactly once. It is not academic box-checking: it is what stops your database from lying to itself when the same fact is updated in one place but not another. Keys A candidate key is a minimal set of columns that uniquely identifies a row, minimal meaning you cannot drop any column from the set and keep uniqueness. A table can have several candidate keys (e.g., user_id and email might both uniquely identify a user). One candidate key is chosen as the primary key; the rest are alternate keys, still worth a unique constraint. A foreign key is a column (or set of columns) in one table that references the primary key of another, enforcing referential integrity: a Loan.book_id must reference an existing Book.id. A composite key is a primary key made of more than one column, common in junction tables (e.g., (student_id, course_id)). Surrogate keys (auto-incrementing integers or Universally Unique Identifiers (UUIDs) with no business meaning) are often preferred over natural keys (like an email or a national ID) because natural keys can change or turn out not to be as unique as assumed. Functional Dependencies A functional dependency A → B means that for a given value of A, there is exactly one corresponding value of B. If student_id → student_name, then knowing the student ID fully determines the name. Functional dependencies are the mathematical bedrock normalisation is built on. A partial dependency exists when a non-key attribute depends on only part of a composite key. A transitive dependency exists when a non-key attribute depends on another non-key attribute rather than directly on the key. 1NF to BCNF 1NF (First Normal Form): every column holds a single, atomic value, with no repeating groups and no comma-separated lists in a cell.. 2NF: 1NF, plus no partial dependencies: every non-key attribute depends on the *whole* primary key, not just part of a composite key.. 3NF: 2NF, plus no transitive dependencies: every non-key attribute depends only on the key, not on another non-key attribute.. Boyce-Codd Normal Form (BCNF): a stricter version of 3NF: for every functional dependency A → B, A must be a candidate key. BCNF resolves rare anomalies that survive 3NF when a table has multiple overlapping candidate keys. Each anomaly normalisation removes has a name: an update anomaly (a fact stored twice can go out of sync), an insertion anomaly (you can't add a fact without an unrelated fact being present), and a deletion anomaly (deleting one fact accidentally erases another). Denormalisation Trade-offs Normalisation optimises for write correctness and storage efficiency; it does not optimise for read speed. Denormalisation (deliberately duplicating data or pre-joining tables) trades some risk of anomalies for fewer joins and faster reads, and is common in reporting tables, caches, and read-heavy services. The rule of thumb: normalise until it hurts reads, then denormalise deliberately and document exactly which anomaly you are accepting and how you will keep the duplicated data consistent (e.g., via triggers, application logic, or scheduled reconciliation). Related CCI capabilities Computer Architecture (Course): (https://www.cambridgecyberinternational.com/en/insights/academy/computer-architecture/). Optics Primer Series (Course): (https://www.cambridgecyberinternational.com/en/insights/academy/optics/). Maths Refresher Series, Finance (Course): (https://www.cambridgecyberinternational.com/en/insights/academy/maths-finance/). System Dynamics (Course): (https://www.cambridgecyberinternational.com/en/insights/academy/system-dynamics/). CCI Lab: Run it, build with it, read the thinking, reuse the data. (https://www.cambridgecyberinternational.com/en/insights/lab/) Choosing a model. Choosing a model. Evaluate relational, document, and graph databases to choose the right model for a problem.. Identify the distinguishing characteristics and trade-offs of relational, document, and graph data stores.. Relational, Document, Graph, Decision criteria Choosing a Model Normalisation and entity-relationship (ER) modeling assume a relational target, but relational is not the only option, and picking the wrong storage model for your access pattern causes pain no amount of tuning fixes. This lesson is about matching the shape of the data and the shape of the queries to the shape of the store. Relational The relational model (tables, rows, foreign keys, joins, transactions) is the default for a reason: it enforces schema and referential integrity at write time, supports flexible ad-hoc queries via joins, and gives strong (ACID) consistency guarantees. It is the right choice when data is naturally tabular, relationships between entities are important and numerous, the query patterns are not fully known upfront, and you need strong consistency across multiple related writes (e.g., financial ledgers, inventory systems, anything with multi-table transactions). Its weakness: joins across many tables at scale can get expensive, and rigid schemas resist rapidly-changing or highly variable data shapes. A well-known example is a banking core system, where every deposit and withdrawal must apply consistently and never partially, even under concurrent load or a mid-transaction failure. Document A document store (e.g., MongoDB, Couchbase) keeps semi-structured records, typically JavaScript Object Notation (JSON)-like documents, where related data is nested inside a single document rather than spread across tables. It shines when an entity's data is naturally read and written as one unit (a product catalog entry with variable attributes, a user profile with nested preferences), the schema varies between records or evolves quickly, and the dominant access pattern is 'fetch this one document by its key' rather than complex cross-entity joins. A common example is a content management system, where each article type carries different fields and the schema evolves as new content formats are introduced. Its weakness: joining across documents is awkward or requires denormalising data into each document (accepting update anomalies as a deliberate trade-off), and multi-document transactional guarantees are typically weaker or more expensive than in relational systems. Graph A graph database (e.g., Neo4j, Amazon Neptune) models data explicitly as nodes and edges, where the relationships themselves are first-class citizens with their own properties. It is the right choice when the *questions you ask are about the relationships*, such as shortest path, degrees of connection, community detection, or recommendation via traversal ('friends of friends who bought X'), and especially when relationships are deep, variable in depth, or many-to-many in ways that would require an explosion of joins in a relational model. Its weakness: graph databases are usually a poor fit for simple tabular reporting or bulk aggregate queries, and the tooling ecosystem is smaller than relational's. Decision Criteria Ask, in order: (1) What are the dominant access patterns: point lookups, joins, or traversals? (2) How variable is the schema across records? (3) What consistency guarantees do writes need? (4) How deep and important are the relationships to the actual questions being asked? A system can legitimately use more than one model, an approach called polyglot persistence, using relational for the ledger, document for the catalog, and graph for the recommendation engine, provided the added operational complexity is justified by real, distinct access patterns rather than novelty. Related CCI capabilities Computer Architecture (Course): (https://www.cambridgecyberinternational.com/en/insights/academy/computer-architecture/). Optics Primer Series (Course): (https://www.cambridgecyberinternational.com/en/insights/academy/optics/). Maths Refresher Series, Finance (Course): (https://www.cambridgecyberinternational.com/en/insights/academy/maths-finance/). System Dynamics (Course): (https://www.cambridgecyberinternational.com/en/insights/academy/system-dynamics/). CCI Lab: Run it, build with it, read the thinking, reuse the data. (https://www.cambridgecyberinternational.com/en/insights/lab/)