Design process:

• Determine the purpose of database. Define the access patterns in terms of most common queries, type of data/views required, whether real time or async, etc.
• Find and gather the information required to store in the database. Give the information a structure in terms of tables/collections.
• Define an item: a row or a document. Avoid calculated values as columns, keep the most atomic form of information in a single column(1NF)
• Specify primary keys for each table/collection. Ensure primary keys are unique, not null and do not change for an item.
• Modelling relationships between tables/collections:

One to one: When you don't want to store an information in the main table. This can happen when you want a column to be separate because of security reasons(a separate passwords collection) or when the column is sparse and storing it in the main table will bloat it up. There should be a common column between two tables for one to one and one to many relationships

One to Many: To represent a one-to-many relationship in your database design, take the primary key on the "one" side of the relationship and add it as an additional column or columns to the table on the "many" side of the relationship

Many to Many: Using a junction table. Use a composite key(foreign keys of both the tables) as the primary key.

• Refining the database. Adding/removing tables/columns. Try to run a sample queries and find out bottlenecks.
• Applying normalization rules.

1NF: Atomic values in columns.

2NF: In case of a composite primary key, any non-key fields must depend on entire primary key, not a subset of it.

3NF: Any non-key field must be dependent only on the primary key, not any other non-key field.

Keys

Unique, never changing and never null. Ensure integrity, fast queries and lookups, uniqueness.

Superkey: Any column, or a combination of columns that can uniquely identify a row in the DB. Candidate key: The minimum number of columns that can uniquely identify a row in the DB. These are used in design only and have no practical counterpart.

Primary key: Natural or surrogate. Be careful with natural keys: what if they can be changed.. Like in Instagram, a user can change the username. This might break foreign key constraints in referenced tables. Primary keys have the main index on them. The candidate keys not chosen for being the primary key are called alternate keys. Indexing the alternate keys might help.