Meta tags:
description= Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.;
Headings (most frequently used words):
normal, form, one, to, many, relations, why, foreign, keys, eliminate, redundant, data, dependent, on, key, drizzle, fundamentals, normalization, database, relationships, not, polymorphic, 1nf, first, atomic, values, 2nf, second, part, of, the, 3nf, third, non, attributes,
Text of the page (most frequently used words):
and (62), table (62), the (55), key (49), data (38), foreign (36), one (35), you (29), many (26), varchar (25), primary (24), #database (23), can (23), are (21), product_id (20), keys (17), into (16), zip_code (16), relationships (15), have (15), for (14), each (14), that (14), relationship (13), orders (12), integrity (12), insert (12), this (12), integer (12), create (12), 2023 (12), order_id (11), related (11), products (11), might (11), but (11), city (11), drizzle (11), more (10), not (10), record (10), 255 (10), state (10), polymorphic (9), use (9), relations (9), customer (9), composite (9), normalization (9), dependent (9), order (8), different (8), non (8), with (8), multiple (8), values (8), side (8), suppliers (8), product_price (8), product_name (8), instead (7), tables (7), why (7), information (7), 12345 (7), supplier_id (7), zip_codes (7), a123 (7), level (6), system (6), types (6), about (6), systems (6), relational (6), between (6), update (6), your (6), records (6), column (6), courses (6), students (6), supplier_name (6), attributes (6), 101 (6), order_date (6), quantity (6), application (5), sql (5), constraints (5), same (5), user (5), separate (5), these (5), they (5), like (5), single (5), when (5), from (5), referential (5), consistency (5), explanation (5), databases (5), time (5), just (5), part (5), customers (5), example (5), our (5), value (5), think (5), junction (5), categories (5), place (5), most (5), them (5), normal (5), anytown (5), 1200 (5), laptop (5), 2nf (5), address (5), postgres (5), postgresql (5), complex (4), often (4), enforcing (4), way (4), comments (4), having (4), columns (4), scenarios (4), examples (4), also (4), legacy (4), performance (4), where (4), high (4), write (4), very (4), cases (4), easier (4), understand (4), schema (4), visual (4), delete (4), prevent (4), customer_id (4), goal (4), references (4), name (4), books (4), product (4), referencing (4), employees (4), only (4), 67890 (4), beta (4), 100 (4), 3nf (4), c789 (4), b456 (4), orderitems_2nf (4), 1nf (4), query (4), anomalies (4), migrations (4), advanced (3), user_id (3), could (3), etc (3), imagine (3), activity (3), details (3), some (3), their (3), rules (3), such (3), need (3), logic (3), scenario (3), overhead (3), distributed (3), transactions (3), maintain (3), less (3), checks (3), every (3), while (3), within (3), new (3), how (3), crucial (3), exists (3), constraint (3), orphaned (3), break (3), queries (3), become (3), set (3), means (3), because (3), enrollments (3), course_id (3), student_id (3), date (3), two (3), authors (3), author (3), employee (3), let (3), parking (3), tutorials (3), gamma (3), ltd (3), inc (3), acme (3), corp (3), otherville (3), remove (3), problem (3), other (3), form (3), 103 (3), 102 (3), keyboard (3), mouse (3), orderitems_unnormalized (3), redundancy (3), studio (3), doesn (2), direct (2), users (2), notifications (2), article_id (2), video_id (2), articles (2), videos (2), comment (2), common (2), creating (2), share (2), concept (2), point (2), check (2), out (2), planetscale (2), team (2), issues (2), inconsistencies (2), external (2), potentially (2), ensure (2), stores (2), cross (2), node (2), introduce (2), nodes (2), needed (2), cloud (2), environments (2), extremely (2), real (2), there (2), those (2), actively (2), defining (2), checking (2), over (2), connected (2), technical (2), design (2), prevents (2), still (2), without (2), accidentally (2), important (2), logical (2), context (2), down (2), what (2), try (2), core (2), valid (2), maintaining (2), here (2), partially (2), student (2), course (2), unique (2), good (2), generated (2), main (2), called (2), book (2), written (2), department (2), belongs (2), say (2), organization (2), security (2), assigned (2), space (2), account (2), forms (2), guides (2), achieve (2), attribute (2), store (2), eliminate (2), redundant (2), decimal (2), fully (2), appears (2), entire (2), expand (2), per (2), applies (2), all (2), street_address (2), normalized (2), pieces (2), get (2), helps (2), places (2), manage (2), fundamentals (2), web (2), goodies (2), dark (2), light (2), extensions (2), effect (2), typebox (2), custom (2), overview (2), upgrade (2), cockroachdb (2), mssql (2), singlestore (2), sqlite (2), mysql (2), handled, using, features, depending, specific, standard, built, support, regular, event_id, events, message, notifiable_id, notifiable_type, notification_id, notification, event, comment_text, commentable_id, commentable_type, comment_id, content, reviews, log, has, type, things, relate, approach, activities, allows, entities, flexible, adaptable, kinds, commonality, great, explanations, article, consistently, adhere, enforced, imposing, lead, import, necessitate, transformation, management, carefully, evaluate, quality, sources, rely, etl, processes, strictly, integrating, older, nosql, flat, files, apis, integration, significant, complexity, validating, requires, communication, leading, increased, latency, performant, than, local, architectures, eventual, models, considered, alternatives, across, clusters, sharded, microservices, needs, perform, small, noticeable, volume, transactional, logging, frequency, trading, platforms, massive, iot, ingestion, highly, beneficial, reconsider, caution, typically, edge, involve, trade, offs, making, robust, reliable, understandable, guaranteeing, essence, makes, evolve, developers, quickly, grasp, parts, see, immediately, tells, clear, structural, indicator, enforcement, documentation, facilitating, understanding, longer, inconsistency, exist, suddenly, preventing, orphans, structure, lose, unreliable, reports, inaccurate, corresponding, would, orphan, happening, control, happens, via, cascade, null, remain, consistent, suggestion, enforces, becomes, aware, hey, want, enforce, must, correspond, discussed, language, telling, explicitly, validation, end, much, larger, purpose, fundamentally, simply, validate, ensuring, enter, actually, another, right, mechanism, duplicate, enrollment_date, optional, practice, identity, always, connects, credits, directly, implemented, associative, bridging, acts, intermediary, link, both, belong, shirt, clothing, summer, wear, category, contain, enroll, enrolled, bidirectional, departments, simplify, now, parent, child, sometimes, split, wide, better, reasons, splitting, parkingspaces, spaces, website, exactly, profile, userprofiles, containing, detailed, profiles, exclusive, pairing, additional, won, cover, will, dedicated, section, dknf, etnf, eknf, 6nf, 5nf, 4nf, know, put, keyed, itself, solution, supplier, including, eliminating, transitive, dependencies, third, remains, notice, repeated, whenever, which, partial, dependency, tracks, items, though, simplified, assume, once, clarity, order_items, made, ensures, second, customers_1nf, info, customers_unnormalized, unnormalized, violates, 123, usa, should, hold, indivisible, repeating, groups, cell, first, atomic, described, terms, beyond, quite, ideas, straightforward, generally, logically, structured, modify, losing, valuable, something, seemingly, unrelated, deletion, rows, difficulty, adding, missing, insertion, chance, updated, everywhere, improves, storing, changes, refer, minimizing, repetition, reduces, process, organizing, reduce, duplication, improve, accuracy, tidying, messy, filing, cabinet, sorts, papers, crammed, folder, organize, folders, make, everything, find, world, especially, absolutely, fundamental, connections, links, life, people, objects, model, work, together, christmas, deal, analytics, dollar, stats, giving, off, turso, scaler, pro, year, partner, backer, run, gateway, package, extension, benchmarks, discord, twitter, sponsor, graphql, eslint, plugin, arktype, valibot, zod, validations, jit, mappers, codecs, read, replicas, dynamic, building, cache, batch, operations, serverless, magic, operator, aliases, joins, utils, filters, select, access, versioning, generators, seeding, config, mobile, teams, export, pull, push, migrate, generate, row, rls, schemas, views, sequences, indexes, proxy, aws, api, netlify, bun, nile, pglite, xata, supabase, prisma, vercel, neon, connect, connection, updates, gotchas, latest, releases, sustainability, started, meet, 35k, docs, merged, alternation, engine, release, orm,
Text of the page (random words):
f normal forms 1nf 2nf 3nf and beyond while the details can get quite technical the core ideas are straightforward 1nf first normal form atomic values goal each column should hold a single indivisible value no repeating groups of data within a single cell example instead of having a single address column that stores 123 main st city usa you d break it down into separate columns street_address city state zip_code unnormalized violates 1nf create table customers_unnormalized customer_id integer primary key name varchar 255 address varchar 255 problem multiple pieces of info in one column normalized to 1nf create table customers_1nf customer_id integer primary key name varchar 255 street_address varchar 255 city varchar 255 state varchar 255 zip_code varchar 10 2nf second normal form eliminate redundant data dependent on part of the key goal applies when you have a table with a composite primary key a primary key made up of two or more columns 2nf ensures that all non key attributes are fully dependent on the entire composite primary key not just part of it imagine we have a table called order_items this table tracks items within orders and we use a composite primary key order_id product_id because a single order can have multiple of the same product though in this simplified example let s assume each product appears only once per order for clarity but the composite key logic still applies expand for visual example create table orderitems_unnormalized order_id integer product_id varchar 10 product_name varchar 100 product_price decimal 10 2 quantity integer order_date date primary key order_id product_id composite primary key insert into orderitems_unnormalized order_id product_id product_name product_price quantity order_date values 101 a123 laptop 1200 00 1 2023 10 27 101 b456 mouse 25 00 2 2023 10 27 102 a123 laptop 1200 00 1 2023 10 28 103 c789 keyboard 75 00 1 2023 10 29 orderitems_unnormalized pk order_id product_id product_name product_price quantity order_date 101 a123 laptop 1200 00 1 2023 10 27 101 b456 mouse 25 00 2 2023 10 27 102 a123 laptop 1200 00 1 2023 10 28 103 c789 keyboard 75 00 1 2023 10 29 problem notice that product_name and product_price are repeated whenever the same product_id appears in different orders these attributes are only dependent on product_id which is part of the composite primary key order_id product_id but not the entire key this is a partial dependency to achieve 2nf we need to remove the partially dependent attributes product_name product_price and place them in a separate table where they are fully dependent on the primary key of that new table normalization to 2nf visual explanation 1 m products orderitems_2nf pk product_id pk order_id product_id product_name quantity product_price order_date fk product_id create table products product_id varchar 10 primary key product_name varchar 100 product_price decimal 10 2 create table orderitems_2nf order_id integer product_id varchar 10 quantity integer order_date date primary key order_id product_id composite primary key remains foreign key product_id references products product_id foreign key to products insert data into products insert into products product_id product_name product_price values a123 laptop 1200 00 b456 mouse 25 00 c789 keyboard 75 00 insert data into orderitems_2nf referencing products insert into orderitems_2nf order_id product_id quantity order_date values 101 a123 1 2023 10 27 101 b456 2 2023 10 27 102 a123 1 2023 10 28 103 c789 1 2023 10 29 3nf third normal form eliminate redundant data dependent on non key attributes goal remove data that is dependent on other non key attributes this is about eliminating transitive dependencies problem let s say we have a suppliers table we store supplier information including their zip_code city and state supplier_id is the primary key create table suppliers supplier_id varchar 10 primary key supplier_name varchar 255 zip_code varchar 10 city varchar 100 state varchar 50 insert into suppliers supplier_id supplier_name zip_code city state values s1 acme corp 12345 anytown ny s2 beta inc 67890 otherville ca s3 gamma ltd 12345 anytown ny suppliers pk supplier_id supplier_name zip_code city state s1 acme corp 12345 anytown ny s2 beta inc 67890 otherville ca s3 gamma ltd 12345 anytown ny solution to achieve 3nf we remove the attributes dependent on the non key attribute city state dependent on zip_code and put them into a separate table keyed by the non key attribute itself zip_code normalization to 3nf visual explanation 1 m zip_codes suppliers pk zip_code pk supplier_id city supplier_name state fk zip_code create table zip_codes zip_code varchar 10 primary key city varchar 100 state varchar 50 create table suppliers supplier_id varchar 10 primary key supplier_name varchar 255 zip_code varchar 10 foreign key to zip_codes foreign key zip_code references zip_codes zip_code insert data into zip_codes insert into zip_codes zip_code city state values 12345 anytown ny 67890 otherville ca insert data into suppliers referencing zip_codes insert into suppliers supplier_id supplier_name zip_code values s1 acme corp 12345 s2 beta inc 67890 s3 gamma ltd 12345 good to know there are additional normal forms such as 4nf 5nf 6nf eknf etnf and dknf we won t cover these here but we will create a dedicated set of tutorials for them in our guides and tutorials section database relationships one to one in a one to one relationship each record in table a is related to at most one record in table b and each record in table b is related to at most one record in table a it s a very direct exclusive pairing use cases examples user profiles and user account details think of a website each user account in a users table might have exactly one user profile in a userprofiles table containing more detailed information employees and parking spaces an employees table and a parkingspaces table each employee might be assigned at most one parking space and each parking space is assigned to at most one employee splitting tables for organization sometimes you might split a very wide table into two for better organization or security reasons maintaining a 1 1 relationship between them table a one side table b one side pk a fk a foreign key referencing table a one to many in a one to many relationship one record in table a can be related to many records in table b but each record in table b is related to at most one record in table a think of it as a parent child relationship use cases examples customers and orders one customer can place many orders but each order belongs to only one customer authors and books one author can write many books but let s simplify for now and say each book is written by one primary author departments and employees one department can have many employees but each employee belongs to only one department table a one side table b many side pk a fk a foreign key referencing table a one many many to many in a many to many relationship one record in table a can be related to many records in table b and one record in table b can be related to many records in table a it s a more complex bidirectional relationship use cases examples students and courses one student can enroll in many courses and one course can have many students enrolled products and categories one product can belong to multiple categories e g a t shirt can be in clothing and summer wear categories and one category can contain many products authors and books a book can be written by multiple authors and an author can write multiple books table a many side junction table table b many side pk a fk a fk b fk b many junction many many to many relationships are not directly implemented with foreign keys between the two main tables instead you need a junction table also called an associative table or bridging table this table acts as an intermediary to link records from both tables table for students many side create table students id integer primary key name varchar 255 table for courses many side create table courses id integer primary key name varchar 255 credits integer junction table enrollments connects students and courses m m relationship create table enrollments id integer generated always as identity primary key optional but good practice for junction tables student_id integer course_id integer enrollment_date date composite foreign keys often part of a composite primary key or unique constraint foreign key student_id references students id foreign key course_id references courses id unique student_id course_id prevent duplicate enrollments for the same student and course why foreign keys you might think of foreign key constraints as simply a way to validate data ensuring that when you enter a value in a foreign key column that value actually exists in the primary key column of another table and you d be partially right this value checking is the mechanism foreign keys use but it s crucial to understand that this validation is not the end goal it s the means to a much larger purpose foreign key constraints are fundamentally about 1 explicitly defining and enforcing relationships we ve discussed relationships like one to many between customers and orders a foreign key is the sql language s way of telling the database hey database i want to enforce a 1 m relationship here every value in the customer_id column of the orders table must correspond to a valid customer_id in the customers table it s not just a suggestion it s a constraint the database actively enforces the database becomes relationship aware because of the foreign key 2 maintaining referential integrity this is the core of data integrity in the context of relationships referential integrity means that relationships between tables remain consistent and valid over time foreign keys prevent orphaned records what s an orphaned record in our customer order example an order that exists in the orders table but doesn t have a corresponding customer in the customers table would be an orphan foreign keys prevent this from happening or control what happens if you try to delete a customer with orders via cascade set null etc why is preventing orphans important orphaned records break the logical structure of your data if you have an order without a customer you lose crucial context queries become unreliable reports become inaccurate and your application s logic can break down example without a foreign key you could accidentally delete a customer from the customers table while their orders still exist in the orders table suddenly you have orders that point to a customer that no longer exists a foreign key constraint prevents this data inconsistency 3 facilitating database design and understanding foreign keys are not just about technical enforcement they are also a crucial part of database design documentation when you see a foreign key in a database schema it immediately tells you table x is related to table y in this way it s a clear visual and structural indicator of relationships this makes databases easier to understand maintain and evolve over time new developers can quickly grasp how different parts of the database are connected in essence foreign key constraints are not just about checking values they are about defining the rules of your data relationships actively enforcing those rules at the database level guaranteeing data integrity and consistency within those relationships making your database more robust reliable and understandable why not foreign keys while highly beneficial there are some scenarios where you might reconsider or use foreign keys with caution these are typically edge cases and often involve trade offs 1 performance overhead in very high write environments scenario extremely high volume transactional systems e g real time logging very high frequency trading platforms massive iot data ingestion explanation every time you insert or update data in a table with a foreign key the database system needs to perform checks to ensure referential integrity in extremely high write scenarios these checks can introduce a small but potentially noticeable performance overhead 2 distributed database systems and cross node foreign keys scenario systems where data is distributed across multiple database nodes or clusters common in sharded databases cloud environments and microservices explanation cross node foreign keys can introduce significant complexity and performance overhead validating referential integrity requires communication between nodes leading to increased latency distributed transactions needed to maintain consistency are also more complex and can be less performant than local transactions in such architectures application level data integrity checks or eventual consistency models might be considered alternatives 3 legacy systems and data integration with non relational data scenario integrating a relational database with older legacy systems or non relational data stores e g nosql flat files external apis explanation legacy systems or non relational data might not consistently adhere to the referential integrity rules enforced by foreign keys imposing foreign keys in such scenarios can lead to data import issues data inconsistencies and might necessitate complex data transformation or application level integrity management instead you might need to carefully evaluate the data quality and consistency of the external sources and potentially rely on application logic or etl processes to ensure data integrity instead of strictly enforcing foreign keys at the database level you can also check out some great explanations from the planetscale team in their article polymorphic relations polymorphic relationships are a more advanced concept that allows a single relationship to point to different types of entities or tables it s about creating more flexible and adaptable relationships when you have different kinds of data that share some commonality imagine you have an activities log an activity could be a comment a like or a share each of these activity types has different details instead of creating separate tables and relationships for each activity type and the things they relate to you might use a polymorphic approach common scenarios examples comments reviews a comment might be related to different types of content articles products videos etc instead of having separate article_id product_id video_id columns in a comments table you can use a polymorphic relationship comments pk comment_id commentable_type polymorphic relationship commentable_id user_id comment_text articles products videos pk article_id pk product_id pk video_id notifications a notification could be related to a user an order a system event etc notifications pk notification_id notifiable_type polymorphic relationship notifiable_id user_id message users orders system events pk user_id pk order_id pk event_id polymorphic relationships are more complex and are often handled at the application lev...
|
