Keys to Building a Successful Database
When it comes to programming as a hobby, I love to just dive in and make fly-by changes as I go. I thought I was good at just adding and changing features on a whim as I figured out what the final product in my head started to form on the screen. When I started professionally programming, I knew that starting with consumer requirements and ending with what I thought fit nicely on the screen wasn’t going to cut it. This starts at the database level and creating a well-defined Entity-Relationship Diagram, also known as an ERD. Also really just a confusing phrase to make an aesthetic acronym to define how objects in a database are shown and interact with each other. I’ll be calling this a Database Diagram and hopefully I can get DD to catch on.
Creating a Meaningful Database Diagram
Spending the time to plan and lay out your projects requirements will make the development of a database much less painful. By creating a diagram showing how you want your database to interact, you have a picture and mindset to form while programming. This all starts with determining what data you want to store, and how you want it organized.
Determining Models:
The models you create should create the blueprint for any individual keys in an object, meaning you shouldn’t attributes in a model that would be stored as a list. This will allow your data to be scalable if you don’t know how many of an item will be stored in an object, or if needs change as your application grows.
For example, if you had a Child model, you might initially think to create it with the attributes father and mother. This might work if you are creating a birth certificate, but in many real-world examples, it is more complex than that. Creating a Parent model would allow for you to have a separate entity that could be more versatile for scenarios where you only have 1 parent, you have two parents who may identify as the same gender, or even if you have a group of parental figures raising the child.
Establishing Attributes:
Get Eric Krukowski’s stories in your inbox
Join Medium for free to get updates from this writer.
Once you have your models identified, you can set what attributes you want to use in them. Following on my Child model, you may want to have attributes for their first name, last name, and their date of birth. If it were being used for a daycare, you might want attributes for an emergency contact or their favorite toy or activity. You’ll also want to store the type of key (if it’s a primary or foreign key) as well as the data type you will be using for the attribute. The following table might look something like this:
Making Relations:
Once you have your models laid out, you’ll want to create relations between them. The main database relations are one-to-one, one-to-many, and many-to-many. For a one-to-one relationship, each model would have a foreign key linking to the id of the other model, and an arrow pointing between the two models to show the connection. For example, if you wanted several attributes that wouldn’t normally be pulled, you could store them in another Model and link the id of the child to that model. In a one-to-many relationship, the “many” would have a foreign key to the “one” model, with an arrow pointing to the one model. An example of a one-to-many relationship could be for reporting notes on the child’s behavior for that day. The Child (one) would have several Notes (many). When creating a many-to-many relationship, like for the Parent and Child models, a join table is an effective way to link the relations. A simple join table could include 3 attributes: an id for the relationship that serves as a primary key, the child_id that links to the Child model, and the parent_id that links to the Parent model. The final depiction of your Database Diagram might look something like this:
Explaining Models and Relations:
After you create your visual Database Diagram, document this in text. Specifically writing out how you want each model to interact with each other is helpful. For example: “A child will have a single Child_Metadata, a child will have zero to many notes, many children will have zero to many parents.” Doing this for each of your models will help when putting these models and relationships into code. This could be done before creating the models, depending on how you process and analyze ideas, but I often have a picture in my head of how I want things organized, so creating pictures helps me more.
Taking a little time to write out a Database Diagram (DD) will ultimately save you time and energy when you’re implementing it into code. There’s also software that can assist with creating these diagrams. Figma is a great tool for creating visuals and relations and includes templates for various (including ERD if you can still remember that acronym) applications. So next time you go to start your next project, whip out that Database Diagram and see how smooth the process follows!
