SQL is known for being a query based language built for database communication, and with the intention of facilitating security management. A feature that assists in the framework of SQL databases is known as schema design. Today I will be diving into an in-depth look and analyses of schemas, how to design schemas, and the benefits of schemas in databases and as a foundation for SQL itself. Let’s get started.
What are schemas?
You can think of the dynamic between a schema and database to be similar to a schematic and an assembler. Schemas act as a framework for not only object owner to have a better understanding, but for the database itself. SQL has a built in system which allows for the database and schema to work hand-and-hand to keep communication running.
Reading a schema
When taking a look at the various tables and columns with connected lines, to someone who has never seen a schema before, they may not know the meaning behind it all. So let’s take a look and see what this entails:
Entity: The table of the database.
Field: The column of the entity.
Record: The rows/data values given to their respected fields in a entity.
Primary Key: A value that makes a record unique.
Composite/foreign key: When the data values from one field is needed information for a remote field.
Simple Key: A record which has only one primary key.
Schema Relationships:
Another useful feature that schemas can provide is the ability to have dynamic entity relationships. For this portion of the blog I will go over several examples of different types of schema relationships:
For the first example shown above is the most basic schema relationship, one-to-one. This is where the foreign key that holds one value being passed on from a parent entity (players) to the child entity (games).
The example shown above depicts the data values of the field full_name in the Runners entity as a foreign key for the field runners in the entity Race. This instance is known as a one-to-many relationship, due to how the runners values are from the records of a remote field only holding one value.
The last example shows how a many-to-many relationship would look. Because there are two fields that hold many values in both entities, in order to keep the time complexity constant and to prevent data from overloading, there would need to be a third-party entity which would hold the ids of both foreign keys.
What to keep in mind about Schemas:
1: Every Database should have a purpose: When making a schema, you should not have any additional or excess data values in the database.
2: Make Keys Equate to Integers: Keys should always equate to integers in your database. That way, the schema will be able to capture the said integer and trace data in a better efficiency.
3: ALL records must have unique keys (no dupes): Never make records have duplicate data. Every field should be unique is some way, where it be the id, name, type, etc.
