Let’s start with the basics. In a relational database, data is spanned across several tables. A table is setup with rows and columns very similar to a standard spreadsheet. When handling the tables, developers have to account for the time to access the data as well as ensure the data is available to multiple programs and multiple forms within those programs. Too much data in a single table can slow down queries and reports. This is where the relationship comes in. Portions of each table relate to each other, allowing an application to pull data from multiple tables.
For example: TeamWORKS Locations are an essential part of work orders, assets, PMs, utilities, event scheduling and almost every other module of the suite of applications. Everything has to belong to something. That’s what makes it a “relational” database. TeamWORKS has chosen the Locations to be the common factor and the Locations table is the foundation for our relational database. It is easier to create a relationship between the Locations table and other tables than to build the locations over and over again in each table. This allows for certain data to be called upon and reused with relative ease.
As with all relationships, database relationships can get complicated and require some basic rules to stay functioning and healthy. In the database management world we deal with terms like “one to many”, “one to one” and “many to one”. This refers to a single piece of data relating to another or multiple pieces of data and vice versa. Back to our Locations example: a location is present in every work order, asset, PM, utility bill and event. If someone were to inadvertently delete or change the location incorrectly the records referring to that location would lose their relationship to the database and would become orphaned records. This leads to inaccurate reporting and can create errors in the applications.
TeamWORKS strives to improve on ways to prevent these circumstances from happening but must balance that against speed to ensure slow performance does not prevent the users from doing their work. We have to find that happy medium. The key to a good relationship is communication. So if you have a question and are not sure if changing or deleting certain data may cause a problem it is better if you give us a call or send an email to make sure its ok. We’re here to help your relationship be successful!