Foundry – The basics of designing a Database
Before using Foundry to start the database creation process, the database first needs to be designed and mapped out. By planning and drawing out the database structure, users are easily able to visualise how the database will interact and the key fields that are responsible for doing so.
The first step is to decide on the different tables within the database. There are two common major categories of tables: dimensions and facts. The dimension tables contain all the referential data. Common examples are accounts, employees, products, etc. The fact tables are data entry tables, which reference the dimension tables and should therefore be created after, such as an Invoice table. It’s good practice to name each of the tables uniquely.
Once the tables have been decided, the next step is to decide which fields each of the tables will consist of. Each table should have at least one unique field, which will act as a primary key and can be used as an identifier for the table. If a combination of fields is needed to act as an identifier, note that down as well because this will become a composite/compound primary key.
When naming the fields, fields within a table shouldn’t need to have the table name (e.g. in the Product table, Product Description should just be named as Description). To future proof your dimension tables, an extra field called “Active” should be created, this will allow users to specify that a record is no longer active without having to delete the record. For example, an Employee may be no longer working at the company and is marked as inactive, instead of being deleted. This is important because their details might be recorded in other tables, and deleting their record would damage the integrity of the historical data.
When deciding on the fields for each table, it is good practice to not have many repeated fields, as redundant fields can decrease the integrity of the database. For example, the Invoices table will need fields such as Account name and Account Company. However, what would happen if in the future, the Account name were to change, all the corresponding records would need to be manually changed and any missed record would cause the data to be inaccurate, which reduces the integrity of the database.
The ideal way to prevent this from happening is to normalize your tables. Take those fields that are repeated and make them into a separate dimensions table, and then join the original table to the newly created one. In this case, an Accounts table is created with Account name, Account company and other details, along with an ID. Those fields can then be removed from the Invoices table and instead be replaced by Accounts ID. A join can then be created between the Account table and the Invoices table, which will allow the Invoices table to always access accurate Account details.
Once all the tables and fields have been decided upon, identify how the tables will relate to each other, like mentioned before with the Invoices and Accounts tables. Commonly, the fact tables will be the ones referencing fields in the dimension tables.
Simple example of Database Structure diagram/mapping. The dimension tables are on the right and the fact table is on the left. Each table has a primary key/unique identifier (green). The fact table also has foreign keys (orange), which join to the corresponding field in the dimension tables, and allows the fact table to reference their data.
Category: Foundry