Creating a Database Schema

The most irksome component of working with data storage is navigation; too often we find ourselves as data folk dropped into a data souce devoid of any resouces for finding the data needed for our projects.

Like sub-contractors on a development project or medical professionals working with patients, having a record to inform one's next decisions makes all the difference for efficiency and success.

For relational databases, this documentation is called the schema and can exist in a PDF/HTML form as well as be scripted into the database itself for easy reference. Schema's generally include details about tables, views, the fields they contain, and the relationships to other tables. They include information about business rules and constraints, listing each table's keys that protect the data. They also outline the functions and procedures that aid in interacting with the source.

In summary, they're a lifesaver for newly introduced data personnel.

During my education, completed schemas were often referred to and when creating our own databases, we never received hands-on training for creating this documentation.

In designing and building the Wardrobe Database, I knew this area of documentation was something I wanted to learn about and practice. This database is an exercise in practicing as many areas of database management as possible, as well as preparing to offer access to portions of the datasource to the public at some point. A schema naturally fell into both those objectives.

After loads of research and using the schema from Dataedo Adventure Works as a source of inspiration, I came up with the following PDF schema for the Wardrobe Database:

Looking at page 4 (which holds the Entity Relationship Diagram [ERD]), we can see why a schema would be so valuable, what with 17 tables, complex relationships, and only surface level information about the fields therein.

The rest of the schema addresses this necessary level of detail.

Take page 9 for example, which details the "wItem" table. We can see similar information to the ERD (keys, fields, datatypes), but we also learn about which fields are allowed as NULL, what attributes they hold, and their descriptions. Further below, we can see the relationships to other tables and have an example join statemtent for future querries. Finally, key value constraints are listed should a query fail, we can use the error message to localize the issue via the schema.

This is the master reference for creating the database and then interacting with it from here on out. It is an invaluable resource for everyone involved. I enjoyed the challenge of creating it and semi-look forward to maintaining it as the database inevitably evolves.