Data Modeling and Schema Design Questions

Direct answer

An entity-relationship (ER) model has four building blocks: entities (the things you track, like Customer or Order), attributes (their properties), relationships (how entities associate with each other), and cardinality (how many instances of one entity associate with how many of another). A weak entity is one that cannot be uniquely identified by its own attributes alone and borrows part of its identity from an owning entity.

Structured elaboration

• Entities: independent objects or concepts in the domain (Customer, Order, Product). Drawn as rectangles; each instance is a row once the model becomes a table.
• Attributes: properties of an entity or relationship. They can be simple (atomic, like email), composite (address split into street/city/zip), multivalued (phone_numbers), or derived (age computed from birth_date). Exactly one attribute (or a combination) is chosen as the identifying key.
• Relationships: associations between entity instances (a Customer places an Order). Relationships can be unary (an Employee manages another Employee), binary (the common case), or n-ary (rarer, involving three or more entities at once, such as Supplier-Part-Warehouse).
• Cardinality and participation: cardinality states the maximum number of instances on each side of a relationship (one-to-one, one-to-many, many-to-many); participation states whether that side is mandatory (every instance must participate) or optional.
• Weak entities: an entity with no candidate key of its own. It is existence-dependent on an owning ("strong") entity and its identifier is a composite of the owner's key plus a partial key of its own. Example: OrderLine cannot be identified without knowing which Order it belongs to, so its real key is (order_id, line_number).

Worked example

Consider a simple domain: Customer, Order, and OrderLine.

erDiagram
    CUSTOMER ||--o{ ORDER : places
    ORDER ||--|{ ORDER_LINE : contains
    CUSTOMER {
        int customer_id PK
        string email
        string name
    }
    ORDER {
        int order_id PK
        int customer_id FK
        date placed_at
    }
    ORDER_LINE {
        int order_id FK
        int line_number
        int product_id
        int quantity
    }

Mapping this ER model to a normalized relational schema is mechanical:

1. Every strong entity becomes a table; its identifying attribute becomes the primary key. Customer and Order are strong entities here.
2. A one-to-many relationship (Customer places Order) is realized by putting the "one" side's key as a foreign key on the "many" side: customer_id on orders.
3. A weak entity (OrderLine, which cannot exist or be identified without an Order) becomes a table whose primary key is the composite of the owning entity's key plus the weak entity's own partial key: (order_id, line_number), with order_id also carrying an ON DELETE CASCADE foreign key, since a line item has no meaning once its order is gone.
4. Many-to-many relationships (not shown above, but common, like Product-to-Category) become their own junction table with a composite key of the two participants' foreign keys.

Trade-offs and pitfalls

• A common mistake is treating a weak entity's partial key as if it were globally unique on its own (line_number alone) instead of scoping it to its owner (order_id, line_number); this silently allows line 1 of order A to collide with line 1 of order B if the composite key isn't enforced.
• Mandatory-vs-optional participation is easy to skip during modeling but directly decides whether a foreign-key column is nullable, which in turn affects every downstream query that joins on it.
• Over-eager n-ary relationships are rare in practice; most "three-way" associations decompose cleanly into two binary relationships once you look closely, and forcing a single n-ary relationship where two binary ones would do usually loses information about which pairing caused which fact.

Direct answer

Storing created_at as TIMESTAMP WITHOUT TIME ZONE means the column silently records whatever local time the writer happened to be in, with no record of which timezone that was; the fix is to change the column to TIMESTAMPTZ and normalize all writes to UTC at ingestion, not to try to reconstruct timezone information after the fact from a column that never captured it.

Structured elaboration

• The risk across regions: if application servers in different regions write to this column using their own local clock (or worse, a mix of local time and UTC depending on which service wrote the row), two rows with the identical stored value can represent two genuinely different real-world instants, and there is no way to tell which is which after the fact, because the timezone information was never captured.
• Normalizing for analysis: the only fully correct fix is to know, for each historical row, what timezone or UTC offset it was actually written in; if that's recoverable (e.g., from a separate region column on the same table, or from deployment history correlated with created_at's range), you can backfill a corrected UTC value. If it's not recoverable, the historical data has an irreducible ambiguity that should be documented, not silently "fixed" with a guessed offset.
• Schema change vs. enforce-at-ingestion: the schema itself should change to TIMESTAMPTZ (which in Postgres always stores UTC internally and converts to/from a timezone only at display time), and the application layer must be fixed to always write in UTC (or with an explicit, correct offset) going forward; changing only the column type without fixing the writers just moves the same ambiguity into a differently-typed column.

Worked example

-- schema fix, step 1: correct any KNOWN non-UTC subset FIRST, while the column is still naive,
-- so the later blanket conversion doesn't reinterpret it a second time.
-- Reinterpret the naive value as the writer's actual local time, then re-express it as the
-- equivalent naive UTC wall-clock value (this two-step "AT TIME ZONE" is what actually corrects it):
UPDATE events
SET created_at = (created_at AT TIME ZONE 'America/Los_Angeles') AT TIME ZONE 'UTC'
WHERE region = 'us-west' AND created_at < '2026-03-01';

-- schema fix, step 2: now that every row's naive value is genuinely UTC, do the blanket type change
ALTER TABLE events ALTER COLUMN created_at TYPE TIMESTAMPTZ USING created_at AT TIME ZONE 'UTC';

The USING clause is doing real work here: TIMESTAMP WITHOUT TIME ZONE AT TIME ZONE 'UTC' reinterprets the naive timestamp as if it were UTC and produces a correct TIMESTAMPTZ; if the true original offset was NOT UTC for some rows, that reinterpretation is silently wrong for exactly those rows, which is why any known region-specific writer needs its own explicit correction pass, run BEFORE the blanket conversion, not after. Running the targeted correction after the column is already TIMESTAMPTZ does not fix it: confirmed by executing both orderings, converting a naive '2026-02-15 10:00:00' value that was actually written in America/Los_Angeles (true UTC instant 2026-02-15 18:00:00+00) using the blanket-first, correct-second order in this answer's original text produced 2026-02-15 02:00:00+00, which is wrong in the opposite direction; running the correction first, as shown above, produced the correct 2026-02-15 18:00:00+00.

Trade-offs and pitfalls

• The single most dangerous mistake here is applying one blanket AT TIME ZONE conversion to the whole table when the true originating offset actually varied by region or by writer over time; that "fixes" the type but silently bakes in a wrong absolute time for every row that wasn't actually UTC to begin with.
• A closely related mistake is getting the ORDER of operations backwards: any known region-specific correction must be applied to the still-naive column before the blanket ALTER ... USING ... AT TIME ZONE 'UTC' runs, not after. Once the blanket conversion has already relabeled every naive value as UTC, a single further AT TIME ZONE step applied to the resulting TIMESTAMPTZ does not correctly undo and reapply the right offset; it needs an explicit reversal step, so it is simpler and less error-prone to fix known-wrong subsets first, while they're still naive, and only then run the one blanket conversion.
• If the true originating offset for some historical rows is genuinely unrecoverable, converting them anyway to satisfy the new column type without flagging the uncertainty produces confidently-wrong data, which is worse than leaving a documented gap; a data_quality_note or a separate timezone_confidence flag on the affected range is more honest.
• Going forward, enforcing UTC at the application/ingestion layer (not just at the database schema level) is what actually prevents recurrence; a schema type change alone doesn't stop a misconfigured writer from producing a naive local timestamp that then gets miscast as if it were UTC.

Direct answer

ON DELETE/ON UPDATE actions decide what happens to a dependent row when the row it references is deleted or its key changes: CASCADE propagates the change, SET NULL clears the reference, and RESTRICT/NO ACTION block the change entirely while any dependent rows exist; the right choice depends on whether the dependent row's existence is meaningful without its parent.

Structured elaboration

• CASCADE: deleting a users row also deletes all of that user's orders. Appropriate when the dependent row has no independent meaning without its parent (a user's shopping-cart items, say), but dangerous when the dependent rows themselves have standalone business value (deleting a user should probably not silently delete their entire order history).
• SET NULL: deleting a users row sets orders.referred_by_user_id to NULL instead of deleting the order. Appropriate when the reference is informational, not load-bearing (knowing who referred a customer is nice to have, but an order remains a valid, meaningful record even if the referrer's account is later deleted).
• RESTRICT/NO ACTION: block the delete entirely while any referencing row exists, forcing an explicit decision (reassign or manually remove the dependents first). Appropriate as the default for anything financially or legally significant, where a cascading or silently-nulled deletion could quietly destroy or corrupt a record that must be preserved.

Worked example

For users and orders: orders.user_id should almost certainly be RESTRICT or NO ACTION, not CASCADE, because deleting a user account should never silently delete their entire purchase and payment history; the correct operational flow is to first decide what happens to their orders (anonymize, reassign to a "deleted user" placeholder, or archive them) as an explicit step, not as an automatic side effect of the account deletion. By contrast, cart_items.cart_id referencing a carts row is a reasonable CASCADE: an abandoned cart's line items have no independent meaning once the cart itself is gone.

Trade-offs and pitfalls

• The main operational risk of CASCADE is exactly the "accidental mass-delete" scenario: deleting one row at the top of a deep reference chain can silently delete thousands of rows across many tables with no confirmation step, which is especially dangerous when the cascade chain is several levels deep and not all of it is obvious to whoever issued the original delete.
• Preventing accidental mass-deletes: default to RESTRICT for anything where deletion should require an explicit, reviewed decision, reserve CASCADE for genuinely dependent, no-independent-value child rows, and consider soft-deletes (an is_active/deleted_at flag, with no ON DELETE action ever firing because rows are never physically deleted) for anything where the safest default is "never let this disappear automatically at all."
• SET NULL requires the foreign-key column to be nullable, which is easy to overlook when initially defining the column as NOT NULL for data-quality reasons; if SET NULL is the intended behavior, the column's nullability constraint has to be designed for it from the start, not bolted on later.

Schema: sensor_readings(sensor_id, ts TIMESTAMP, value, quality, ingestion_ts, PRIMARY KEY(sensor_id, ts DESC) or use (sensor_id, ts) with clustering by sensor_id+ts DESC). Columns: sensor_id, ts, value (numeric), status/quality, tags, offset. Primary key choice: composite key with sensor_id first to enable contiguous writes and range scans per sensor. Indexing: clustered/sort key on (sensor_id, ts DESC) for fast latest-value and efficient range scans. Maintain a separate latest_values(sensor_id PK, latest_ts, value, quality) table updated via upserts or streaming to serve instant latest queries. Retention: time-based partitioning per month or per sensor bucket, with TTL job to drop/compact partitions; use downsampling aggregates (hourly/daily) stored in rollup tables. For high write throughput: use batch inserts, append-only writes, partitioning, and use LSM-based stores (Cassandra/ClickHouse/TimescaleDB) or write-optimized engines. Trade-offs: separate latest table gives O(1) reads; background retention/compaction minimizes storage.

Direct answer

A course-enrollment system needs three tables: students, courses, and a junction table enrollments that resolves the many-to-many relationship, carrying enrollment_date and grade as attributes of the enrollment itself (not of either student or course alone).

Structured elaboration

• students(student_id, name, email, ...), courses(course_id, title, credits, ...).
• enrollments(student_id FK, course_id FK, enrollment_date, grade, PRIMARY KEY(student_id, course_id)): the composite primary key both resolves the many-to-many cardinality and structurally prevents a duplicate enrollment (the same student enrolling in the same course twice).
• Indexing at ~10M enrollments: the composite PK already gives you an efficient lookup path for "all courses a given student took" (leading column student_id). For the reverse direction, "everyone enrolled in a given course," add a secondary index on course_id alone, since the PK's B-tree is ordered by student_id first and won't serve that lookup efficiently at scale.

Worked example

CREATE TABLE students (
  student_id BIGINT PRIMARY KEY,
  name       TEXT NOT NULL,
  email      TEXT NOT NULL UNIQUE
);

CREATE TABLE courses (
  course_id  BIGINT PRIMARY KEY,
  title      TEXT NOT NULL,
  credits    SMALLINT NOT NULL
);

CREATE TABLE enrollments (
  student_id      BIGINT NOT NULL REFERENCES students(student_id),
  course_id       BIGINT NOT NULL REFERENCES courses(course_id),
  enrollment_date DATE NOT NULL,
  grade           CHAR(2),
  PRIMARY KEY (student_id, course_id)
);

CREATE INDEX idx_enrollments_course ON enrollments (course_id);

Verified against SQLite (a reasonable proxy for the relational logic; Postgres syntax is equivalent):

sqlite> INSERT INTO students VALUES (1,'Ana','ana@x.com');
sqlite> INSERT INTO courses VALUES (100,'Databases',3);
sqlite> INSERT INTO enrollments VALUES (1,100,'2026-01-10','A');
sqlite> INSERT INTO enrollments VALUES (1,100,'2026-01-11','B');
-- second insert correctly raises a UNIQUE constraint failure on the (student_id, course_id) primary key

This confirms the composite primary key is what prevents the duplicate-enrollment anomaly, not application-level checking alone.

Trade-offs and pitfalls

• At 10M rows, PRIMARY KEY (student_id, course_id) clustering the table by student_id means "all of a student's enrollments" is a cheap range scan, but "all students in course X" without the secondary index would force a full scan; always add the reverse-direction index explicitly rather than assuming the PK covers both directions.
• grade living on the junction table rather than a separate table is deliberate: a grade only makes sense in the context of a specific enrollment, so it is functionally dependent on the full composite key, not on either student_id or course_id alone (this is exactly the 2NF reasoning: don't let grade accidentally end up depending on just one half of the key).
• A tempting shortcut is a single surrogate enrollment_id as the primary key with a separate UNIQUE(student_id, course_id) constraint; that is equally correct and is often preferred if other tables need to reference a specific enrollment by a single-column foreign key, at the cost of one extra join to check whether a student is already in a course.