Table of Contents

Types of Databases

• Relational databases
  • In relational databases, the data is organized in tables.
  • Relations are between tables that share columns.
  • Table columns are called fields (mapped to object fields)
  • Table rows are called records (1 record = 1 inserted object)
• NoSQL databases
  • NoSQL databases have collections instead of tables
  • Documents are stored in collections.

Normalized vs Denormalized DB

• Description
  • Normalized databases are designed to have cleaner organization with no redundant data. Every field of data is only stored once in the database, then references with foreign keys.
  • Denormalized databases have redundant data.
    • Instead of using references, the fields are duplicated. There will be extra tables containing field data copied from other tables.
    • Less organized, but faster and more scalable when fetching data.
• Efficiency
  • Denormalized databases are faster and more scalable than normalized databases.
  • This is because querying a normalized database could require you to do JOINs to complete a query using multiple tables.
    • JOINs are very expensive and slow
    • The bigger your database is, the more JOINs you’ll have to do to satisfy queries, and the slower your database will be.
  • If you query a denormalized database, you won’t have to do joins because there will be a single table containing all the data your query needs.
• How to flatten (denormalize) a database:
  • Wherever there is a relation in the relational DB schema (2 tables having a shared column), add a new table for the shared data.
  • Copy over all the data shared between the tables.
  • When querying the database, SELECT the redundant table instead of doing a join on the original tables.

Designing a DB Schema (Interviews and Development)

• If you’re in an interview, clarify the question before starting.
  • Find out how large the database will be, i.e. how many users it will handle data for.
  • Find out how many locations/buildings/etc. there will be, how many different types of things you’ll be working with, etc.
• To design a small database:

  1. List out each set of core objects you want to track data for, e.g. users, logs, etc. Make a table for each one.

  2. Make unique columns in each table, for the data you want to track.

     • Only include fields you need to remember, not secondary data that you can compute.

  3. Model the relationships between your tables.

     • One-to-Many
     • One-to-One
     • Many-to-Many

  4. Implement the relationships.

     • For One-to-One relationships: Add one object as a column in the other object’s table.

     • For One-to-Many relationships: Add a foreign key to the Many table, pointing to the primary key of the One table.

         ![DBSchema.png](<https://s3-us-west-2.amazonaws.com/secure.notion-static.com/1236bfce-7011-423d-ad27-238f19bb6c31/DBSchema.png>)
       

       • Each log belongs to a single user. One user can have many logs.
       • Each log has a reference to its user.

     • For Many-to-Many relationships: Create a new table with foreign keys pointing to both tables’ primary keys

         ![DBSchemaMM.jpg](<https://s3-us-west-2.amazonaws.com/secure.notion-static.com/fd756d2a-d37a-4023-b39b-ff1c2b4aee69/DBSchemaMM.jpg>)
       

  5. Walk through all the actions that’ll happen during a user’s operation of the app.

     • List out the data you’ll be manipulating during each action, and how.
     • Add them to your database as fields or tables.
• If you’re designing a large database, you’ll need to denormalize it:
  1. Flatten the data. Design a new, denormalized table with all the essential data that you’ll frequently be querying for.
  2. Copy the data over to the flattened data on a consistent, scheduled basis, e.g. hourly or nightly.
  3. Whenever you need to fetch data from the database, query the denormalized table.

  • Example

    

    • Normalized DB :Every column in every table is a unique field or a foreign key to another table.
    • Denormalized DB: Users table is flattened.
      • Contains redundant data (allocated space, available space) from Storage_restraints table, so you don’t have to query storage table.