> For the complete documentation index, see [llms.txt](https://eric-zhang-seattle.gitbook.io/mess-around/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://eric-zhang-seattle.gitbook.io/mess-around/traditional-db/mysql_schemadesign.md). # Schema design * [Logical design](#logical-design) * [Normal forms with example](#normal-forms-with-example) * [First normal form](#first-normal-form) * [Second normal form](#second-normal-form) * [Third normal form](#third-normal-form) * [Denormalization](#denormalization) * [Use case](#use-case) * [Example](#example) * [ER diagram design with example](#er-diagram-design-with-example) * [Rules](#rules) * [Physical design](#physical-design) * [Select the DB engine](#select-the-db-engine) * [Select the correct data type](#select-the-correct-data-type) * [Integer types](#integer-types) * [Float/Double and decimal types](#floatdouble-and-decimal-types) * [Text](#text) * [Date time](#date-time) * [Best practices for primary key of Innodb engine](#best-practices-for-primary-key-of-innodb-engine) * [Schema design](#schema-design) ## Logical design ### Normal forms with example * Normal forms are a way to measure the redundancy and potential maintenance (Needs to update multiple relation tables). They should not be bindly followed to minimize redundancy because they will also increase query cost. ![](https://1010073591-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-Mk8dv8Mfudl_6ziUzDf%2Fuploads%2Fgit-blob-e2a6980506ed233e330ec3b7401b9f28066dbea6%2Fmysql_schemadesign_norms_original%402x.png?alt=media) #### First normal form * Def: * A relation is in first normal form if every attribute in that relation is atomic and could not be split further. * Examples for violations: * Use Json as an attribute. * Use concatination of multiple attribute as an attribute. * Apply to the example: Attribute property could be split further. ![](https://1010073591-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-Mk8dv8Mfudl_6ziUzDf%2Fuploads%2Fgit-blob-0fb5477bff06c26109055c6a5e4092145c88884d%2Fmysql_schemadesign_firstnorms%402x.png?alt=media) #### Second normal form * Def: * Prerequisite: If a relation satisfy second norm form, then it must satisfy first norm form. * A relation is in 2NF if it has No Partial Dependency, i.e., no non-prime attribute (attributes which are not part of any candidate key) is dependent on any proper subset of any candidate key of the table. * Examples for violations: * Redundancy in tables. Partial primary key could determine another attribute. * Apply to the example: When using attribute "listnumber" + "barcode" as primary key, * "goodsname", "specification", "unit" only relies on "barcode". Separate into a different table. * "supplierId", "supplierName", "stock" only relies on "list number". Separate into a different table. ![](https://1010073591-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-Mk8dv8Mfudl_6ziUzDf%2Fuploads%2Fgit-blob-d3ea193bfa32b0ff5bdc62990c80d2c1169c9de7%2Fmysql_schemadesign_secondnorm%402x.png?alt=media) #### Third normal form * Def: * Prerequisite: If a relation satisfy third norm form, then it must satisfy second norm form. * A relation is not in third normal form if a non-prime attribute is dependent on a non-prime attribute. * Apply to the example: * SupplierName depends on SupplierId ![](https://1010073591-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-Mk8dv8Mfudl_6ziUzDf%2Fuploads%2Fgit-blob-12c5e2afa9dc9ac417a6895ed22f999b1f97ce4d%2Fmysql_schemadesign_thirdNorm%402x.png?alt=media) #### Denormalization **Use case** * When the size of table to join is big (\~million level), then the performance gain from denormalization (\~0.5s) could help. * Typically used in dataware house scenarios where historial data is stored for analytics purpose. **Example** * Within the above table, although quantity \* importprice = importvalue and importvalue is redundant column * If using multiplication of importprice \* quantity, it might not equal to importvalue. E.g. Importprice might change if the supplier has any promotion selling activity. * Vice versa, importprice = importvalue / quantity, importprice is redundant column * Most of financial calculations for cost rely on importprice. If it is a derivative value, it will increase a lot of computational cost overhead. ![](https://1010073591-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-Mk8dv8Mfudl_6ziUzDf%2Fuploads%2Fgit-blob-c737c8bd2df33750dfa9558eaed4d83790609ffb%2Fmysql_schemadesign_exception%402x.png?alt=media) ### ER diagram design with example * Example: * ![](https://1010073591-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-Mk8dv8Mfudl_6ziUzDf%2Fuploads%2Fgit-blob-017a5050ee843cd7ca5c0029c575473267bc8c04%2FER-diagram-original.png?alt=media) ![](https://1010073591-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-Mk8dv8Mfudl_6ziUzDf%2Fuploads%2Fgit-blob-77fef8d3b37eefa029efda82a77926f8a05b50f5%2FER-diagram-schema.png?alt=media) #### Rules * Strong entity: Convert to a table * Weak entity: Convert to a table. Since weak entity cannot exist without a strong entity, the table needs to include a foreign key referencing the strong entity. * 1:M relationship: Use foreign key * M:N relationship: Use a separate table ## Physical design ### Select the DB engine * Comparison between different DB engines: 3.08' ### Select the correct data type * Cheatsheet for all data types: * Determine the type category: number, string, time, binary #### Integer types * TinyInt vs SmallInt vs MediumInt vs Int vs BigInt: Use TinyInt to replace enum. * Int is the most frequently used type, uses 4 bytes. * Guarantee that the data won't overflow first, then save space. #### Float/Double and decimal types * Floating-point types: Float vs Double Use binary to represent decimal and could be inaccurate. * Fixed-Point types: Decimal/Numeric separate the int and Use decimal whenever possible because it is more precise. * Decimal(M,D): M represents digit, D represents decimal #### Text * Types: Tinytext/text/mediumtext/longtext * Varchar vs Char: Char has fixed size length. Space will be padded in the end. 0-255 byte; Varchar has a larger upperbound 65535 byte. * Use char whenever possible. * Use varchar on cases where max size could be much bigger than average and not so often updated (Updating on varchar might break indexes) * Text could not be used as primary key #### Date time * Datetime vs Timestamp vs Date vs Time: * In most case use Datetime (twice the size of Timestamp but unlimited time range. Timestamp only extends until 2038). ### Best practices for primary key of Innodb engine * ### Schema design *