# Database Schemas ## Resources ### [DB Diagram](https://dbdiagram.io/home) Draw entity-relationship diagrams for free ## General ### Nullable one-to-one relationship - Using foreign keys, the standard way is to use the referencing table's primary key as its foreign key into the referenced table. - Semantically, it is more accurately described as "The {owner} has up to 1 {owned}" - e.g. "A user can have up to 1 public key." ![[Screen Shot 2022-06-16 at 11.08.49 PM.png]] ### Non-nullable one-to-one relationship - Place the fields in the same table. - Fields that fall under different categories (e.g. `UserInfo`, `CryptoInfo`) can be defined as nested structs that are flattened when serialized into a database row - "Every user always has one public key - no more, no less." ![[Screen Shot 2022-06-16 at 11.11.17 PM.png]] ### Many-to-one relationship "A user can have zero, one, or multiple public keys." ![[Screen Shot 2022-06-16 at 11.08.25 PM.png]] ### Many-to-many relationship - Create an intermediate table that references the primary keys of the two tables which have the many-to-many relationship. - "A user can have zero, one, or multiple public keys, and each public key can be controlled by multiple users." ![[Screen Shot 2022-06-16 at 11.28.39 PM.png]] A more comprehensive explanation can be found [here](https://fmhelp.filemaker.com/help/18/fmp/en/index.html#page/FMP_Help%2Fmany-to-many-relationships.html%23) ### Self-joining relationships - Some information [here](https://fmhelp.filemaker.com/help/18/fmp/en/index.html#page/FMP_Help%2Frelationships-self-joining.html%23) and [here](https://support.claris.com/s/article/The-Self-Join-Relationship-What-it-is-and-some-examples-of-how-to-use-it-1503692982707?language=en_US)