We developers live in an object oriented world where data is represented as objects in our applications. However, these applications often end up saving their data in SQL databases which are relational, not object oriented. This often causes translation errors and could cost you and your team significant time tracing and debugging. Understanding keys, relationships, and cardinality are essential skills for any developer to be able to create and maintain reliable, consistent, and efficient applications by being able to create and read data models and spot potential problems before they become major bugs.
The most basic component to understanding SQL databases is understanding what a table is. A table is a unique set of data with a consistent number of typed data attributes, which are called Columns. Each table must have a primary key, which is a column which uniquely identifies a row - otherwise, it would be impossible to find a specific row in a table. In the example below, the # denotes the primary key (id), but each the style changes based on what tool you use to make your data model, which is a visual representation of an SQL database which lays out the tables and relationships between them.
The power of relational databases is (perhaps obviously) in the relationships between these tables, which allows you to link together data in numerous ways. When two tables are joined, one is always considered the 'parent' in the relationship and the other is the 'child.' The primary key of the parent table becomes a column in the child table, which is called a foreign key. For instance, a person may have an address, so when a relationship is created between them, the primary key from the parent table (in this case PERSON) is migrated to the child table (in this case ADDRESS) as a foreign key (ADDRESS.person_id).
3. OrdinalityOnce you understand that there is a relationship between two tables, you are ready to build an understanding of what the relationship means. One place to start is considering the Ordinality (also known as Modality) of the relationship, which indicates whether the relationship from the parent table to the child table is optional or not. For instance, the relationship between a book and author is mandatory, while the relationship between a book and an owner would be optional, since a book can be unowned, but somebody must have written it. This is represented by having an O on the relationship if it is optional (Zero or more) or a straight, perpendicular line, which indicates it is required (One or more). Ordinality represents only the parent side of the relationship.
Learn more about modernized technology here:
Interested in training to help advance your agile journey? Click the button to view our current list of public training courses! Use code BLOG10 for 10% off!