When talking about database architecture, there are two main components that get focused upon most. These are how the different databases organize their tables within your database and what kind of layouts or structures they use to connect all these tables together.

The first is typically referred to as having an open-or closed structure. There are some types of databases that are categorized as being more open ended than others. An example of this would be MySQL.

MySQL does not have quite the same level of organization when it comes to using schemas and users. This can create a very difficult time in managing user access for applications that require strong security.

Another type of database that has become increasingly popular is one that uses something called a NoSql database. A no sql database doesn’t have columns like you would find with normal SQL databases.

These types of databases instead use a schema format which isn’t organized into columns but rather just lists under each other. The best known examples of this include MongoDB and CouchDb.

This is because it takes away some of the need to define column names so that you don’t end up with wasted space in your table. What you gain though is the ability to add new attributes without needing to update your current definitions.

That said, even those types of databases will eventually run out of unused attribute positions so it is important to make sure that you aren’t leaving any holes in your data.

Relationship modeling

When it comes to database architecture, one important part is how you organize your data. This is what we refer to as relational structure or relationship modeling.

Database professionals talk about this in terms of normalizing data, but I like to think of it more intuitively by looking at the way people use relationships in nature.

For example, take a look around you. You have plants, animals, and humans. Plants grow roots that stick into soil, they breathe air through their leaves, and they need water and food to survive. Animals eat other things and poach minerals from the ground to build themselves new tissues. And people interact with each other and influence each other’s behavior for various reasons.

All these natural systems are organized in different ways depending on whether there is only one instance of them or there are many. For example, you can’t really compare a plant to an animal, nor can you say that both plants and animals belong in the category “living thing”. But you could make the case that both are categorized under something called “organism” because they both require resources to exist and live.

That brings us back to databases! Just like there are organisms such as people in the world, there are items in a database that relate to other items. A common type of relation is called a one-to-one match. This means that one item links directly to another item.

Fact and Dimension Tables

The next fundamental concept in database theory is fact and dimension tables. A fact table is typically referred to as an item or entity table, while a dimension table is used to define things related to that table.

In our example above, we had a products table which contains information about each product such as name, price, and stock availability. We also have a sales table containing details about each sale such as date, location, and person who made the sale.

A relation between these two tables is found when there is more than one row in the second table referring back to exactly one row in the first table. In other words, there is a relationship if every piece of data in the second table refers back to exactly one row in the first table.

This relational structure is what allows for calculations to be done with the data in both tables. For instance, by taking the number of sales at a given time period and dividing it by the total amount spent per employee, you can determine how well each department did during that time frame.

The ratio obtained from this calculation is called the turnover rate and is useful in determining whether employees are performing their jobs effectively.

Definition of the Fact Table

The next component to discuss is the definition of the fact table, which can be referred to as the “fact store” or sometimes called the “dimension table.” This is an important part of your database because it allows for more advanced querying and analysis of your data.

A fact table contains numerical values that describe some aspect of your dataset, such as revenue for a given quarter or average number of days between calls with a particular supervisor. These numbers are typically organized in columns and displayed along side each other so users can easily compare one value against another.

The beauty of this structure is that you can add new values to the fact table without having to alter the overall schema of the database.

Definition of the Dimension Table

A dimension table is an internal table in your database that contains various attributes for a given row. These are known as dimensions, or fields in this case. The MySQL manual calls these columns field properties.

The most common example of a dimension table is called a “User” table. This has username as a primary key, first name, last name, etcetera under the column headers of user ID, first name, last name, etc.

Other examples include product category tables which have a primary index that is either a natural or artificial key (such as an integer id) along with a parent category and subcategory columns. Another example would be to create a posts-comments schema where comment belongs to both comments and posts, and then having a third table links them together, creating a link between the two. This is a comment_post link.

Star Schema

The other major architecture style for databases is called the star schema or, more commonly, the open data model. This was first popularized in the 1990s when database vendors began to promote it as an efficient way to organize large datasets.

The term “star” refers to the shape that the table takes. Rather than have one column for each piece of information (like a one-to-one relationship), there are several columns that all refer to the same thing. In this case, those columns are only needed because there are so many pieces of the dataset.

By using this structure, you can save disk space by not having to store repeated content multiple times. It also makes searching easier, as you don’t need to search through every field separately.

Some people even describe the star schema as creating a nested set of data, since different parts of the row go into separate fields depending on which part you were looking at before.

Though it has become the de facto standard for organizing big databases, it isn’t without its flaws. For example, if you want to look up just the last name, you will now have to perform two searches instead of one!

Furthermore, switching back and forth between the two tables can be confusing since they do not match up with each other very well. You would probably know which one is which, but someone who does not might get stuck wondering what the heck the second table means.

Entity-relationship model

The entity-relation (or ER) model is one of the most fundamental database architecture concepts out there. It was first formally described in 1972 by Edgar Fowles “[...] as an organization schema for managing data”.

Since then, it has become one of the more widely understood database design principles with many variations.

The term itself comes from the phrase entity–relation–set, where entity refers to discrete units of information (like people or rooms), relation describes how each item is related to another, and set represents all possible relationships among entities.

This concept can be extended to describe other types of relations such as cardinality (how many items belong to something) or conformance (something belongs to something else).

Domain-driven design

In domain-driven software, your applications are built around domains or areas that you want to focus on solving for users. For example, if your application is designed to be used by students, then its domain would be education.

The way to think about this is in terms of what things belong to the domain and how they relate to each other. For instance, an assignment tool for students could have a domain of learning and assignments, but it wouldn’t make much sense without a database somewhere.

That database can become part of the domain of assignments because now everything related to assignments is controlled through the same system — which makes it easier to use!

Architecture isn’t just something that happens at a high level like “we need a database,” architecture is something that applies across all levels of the system. It shapes lower level components, and higher level systems using those components.

In domain-driven design, attention is paid to these relationships so that the systems working together feel integrated and consistent.

Conduct a database review

A close look at how your database functions is known as a database review. While most databases have limited access, there are some tools that allow you to view parts of the database or specific tables.

By looking into these areas, you can learn more about the database and who has access to it. This goes beyond just seeing if someone has accidentally left their phone in the car with the keys still in it, it includes verifying whether someone else could potentially access all important information such as contact details, receipts, and even login credentials for your business!

Database reviews should be done consistently across all devices used by the person being reviewed. It may also be prudent to have the individual do their own database review before performing the test, to ensure nothing was missed.

These reviews can be conducted manually or using software. There are several free and low cost softwares available that can help you perform this function.