Architecture design
It’s important to set out a basic structure of how the database is going to function, before adding more detail later. This is so you know where everything is placed within the database file(s) (including a journal database, if you choose to have one). At this point, you should be deciding what type of database this is going to be, there are a few options, such as: Relational (SQL), Non-Relational (NoSQL), or some form of specialized database. For this, I will be using a relational (SQL) database.
File structure
For this example, there are 3 main “parts” to the file, the schemas, indexes, and tables. You need to decide what order these go in the file. I would recommend them in the following order: schemas, indexes, tables. I suggest this order because the schemas likely to be the least frequently changed, indexes are added in chunks (at least in my example), and tables are the most likely to change. But you can choose to put these in any order you would like.
At this stage, I would recommend actually putting together a HTML table in the README file of your repository. Here’s an example of one with the specific order I mentioned:
<tbody>
<tr>
<th colspan="2">Database file</th>
</tr>
<!-- Basic db metadata -->
<tr>
<td>
Database metadata header
</td>
<td>
Contains metadata for the db, including pointers to other headers
</td>
</tr>
<tr>
<td>
Database config header
</td>
<td>
Contains basic db config
</td>
</tr>
<!-- Schemas -->
<tr>
<th colspan="2">
Schema section
</th>
</tr>
<tr>
<td>
Schema metadata header
</td>
<td>
Contains metadata for the schemas, mainly holding information
about how many schemas there are
</td>
</tr>
<tr>
<td></td>
<td>
These are the least likely to change, which is why they are so high up
in the file
</td>
</tr>
<tr>
<td colspan="2">
Schemas
</td>
</tr>
<!-- Indexes -->
<tr>
<th colspan="2">
Index section
</th>
</tr>
<tr>
<td>
Index metadata header
</td>
<td>
Contains metadata for the indexes, mainly holding information
about how many indexes there are
</td>
</tr>
<tr>
<td></td>
<td>
Indexes are semi-variable width, because they can be wide, but they
are statically sized based on the index metadata, so they are sort of
variable-width, while being statically sized
</td>
</tr>
<tr>
<td></td>
<td>
Indexes are allocated in chunks, rather than individually. Rather than
adding 1 on, it instead adds a chunk of empty indexes
</td>
</tr>
<tr>
<td colspan="2">
Indexes
</td>
</tr>
<!-- Tables -->
<tr>
<th colspan="2">
Index section
</th>
</tr>
<tr>
<td>
Tables metadata header
</td>
<td>
Contains metadata for the tables, mainly holding information
about how many tables there are
</td>
</tr>
<tr>
<td colspan="2">
Tables
</td>
</tr>
</tbody>
</table>You can add more detail later on, in sections below this table. The above table also includes information about the basic database headers, these just contain some metadata that I will talk about later on.
The reason I recommend using HTML tables over Markdown tables for this is so that you have more control over the columns, which allows you to have columns that take the span of 2 or more columns.
Series
Part 1: Environment set up
Part 2: Architecture design (This)
Part 3: What is SQL?
Discover more from WTDawson
Subscribe to get the latest posts sent to your email.
Yes
The discussion on file structure is critical. For high-volume, transactional systems-like managing user accounts and transactions-data integrity is paramount. Thinking about the architecture of a secure platform, knowing where to guide users after they successfully complete a lucky game apk login is just as important as the underlying schema design. Excellent deep dive!