How To Have Filemaker Add Fields Automatically

Mar ix, 2021 xi:42:00 AM / / in Best Practices, Amazon Honeycode /

Common Types of Relationships

Previously, we discussed one-to-many relational databases. By far, this is the well-nigh common type of data relationship that we encounter. We discussed parents and children. A parent record can have many child records, but a child record belongs to one and only one parent record. Those relationships are defined through information. A parent record has a main key that is assigned when a record is created. A child record displays part of the Dna of its parent by carrying the unique parent ID in a foreign primal field. When the kid'southward strange key value matches the parent's master central value nosotros take a link betwixt those records.

Other types of relationships occur too. In this commodity, we'll cover one-to-one and many-to-many database relationships.

Choosing the Right Relationship Type

Those new to database blueprint often have a preconceived notion of what type of human relationship fits a pair of entities. It's easy to jump to a conclusion based on one's perception of how a business operates. As experienced, gnarly developers, we can say for sure your initial instincts are often wrong.

Database relationships reflect the operations of the underlying organization. In many ways, the Entity Relationship Diagram (ERD) describes the operations in tabular array and field terms. Much similar a habitation architect uses engineering science drawings to programme a home that fits the needs of the homeowner, an ERD describes the compages of a business programme and operations.

Imagine, if y'all will, there are 2 entities in your organization. These entities are Sales Reps and Customers. Many new designers might apace leap to the conclusion that a Sales Rep tin can take many Customers, but a Customer belongs to one and only 1 Sales Rep, as shown below:

one-to-many relationship In many use cases, this is the proper relationship between Sales Reps and Customers. Yet, it certainly does non utilize in all scenarios.

Imagine a company like Boeing that sells airplanes. Their customers are large airlines and the armed forces — companies similar American Airlines, Southwest Airlines, the US Regular army and JetBlue.

Customers of this size certainly need more than one Sales Rep each. In fact, they probable have teams of Sales Reps working on multi-billion dollar sales of aircraft. Yet any individual Sales Rep would exist assigned to one and simply ane Customer, as shown beneath:

one-to-many relationship Finally, imagine a scenario where there is a squad approach to sales. Customers tin can buy from multiple Sales Reps on a sales squad, and a Sales Rep tin sell to many different Customers. This is an example of a many-to-many human relationship. Later in this article, nosotros'll discuss properly resolving a many-to-many relationship in an ERD and Honeycode. For now, however, realize that the following ERD blueprint is proper and quite common too.

We promise it's easy to encounter that the relationship between any pair of entities does not have a default reply. We, every bit designers, demand to recognize that relationships are based on the context of the arrangement. Our job as designers and developers is to excerpt this critical information in discussions with users and manage the system we're planning.

One-to-Ane Relationships

Although somewhat uncommon in database systems of the size and complication created in Honeycode, we do see 1-to-one relationships in a scattering of situations in database system development.

In essence, nosotros're saying a single record in table "A" is related to one and only one record in table "B." And a single record in tabular array "B" is related to one and just 1 record in tabular array "A." Information technology's also possible that a record in "A" or "B" is unrelated to a tape in the other. More on this later.

Y'all may ask yourself why these tables are not combined? That's a cracking question, actually, and there are multiple use cases where this makes sense.

Many years ago, we dealt with a client with a very large employee table. There were thousands of employees in this arrangement. A few dozen amid the thousands needed very specialized data in addition to the full general employee data.

Equally information technology turns out, the data was related to security clearances and quite sensitive. Out of an abundance of caution, the visitor insisted that this information be stored in a separate database with very unlike security settings. A record in the "Security Info" table would exist linked to ane and merely i record in the Employees table. A handful of records in the Employees table would exist linked to one and just one record in the "Security Info" table. Most records in the "Employees" tabular array would not exist linked to a record in the "Security Info" table at all as those folks were not involved in classified work.

Data Normalization

Beyond security implications, there's another reason why data scientists would exist inclined to parse out the security information into a divide table.

We're going to get a fleck "geeky" here, so hold on to your keyboards and mice. In more advanced database design, at that place are data normalization rules and requirements. These are related to set theory in math and are used to provide penultimate data efficiency. They tin can become quite complex, actually, and a few really achieve advanced levels of normalization.

Large companies with massive data storage systems long ago realized they could save money on data storage by implementing an efficient data pattern. Imagine a large visitor like Amazon and how much data is stored for past and present orders equally well as products listed for sale.

Some years ago, we were working with a large bank on a department-level solution. Nosotros attended a meeting in an enormous briefing room that had a long wall covered in a massive ERD during that appointment. It turned out this ERD was used but for their credit card operations. The diagram had hundreds of entities and a very circuitous set of defined relationships. Of course, this banking concern employed Ph.D.'southward in math and information science, and it was their task to squeeze out efficiency and minimize redundancy. In this diagram were many one-to-one relationships.

The practiced news is that we, equally Amazon Honeycode developers, probable won't face anywhere almost that level of complication. Our decisions will be based on concern needs and logic rather than complex mathematics.

Example of a 1-to-One Relationship

Recently we worked with a loftier-end sports society. They were looking for a system to track many aspects of their business organisation, including members, membership fees, facilities, programs, and much more.

At this facility, they provided members with lockers. At first, it seemed equally piece of cake every bit assigning a locker number to each member. Simple enough, right?

But as nosotros investigated more than, we discovered that there was additional information collected on each locker. These included the locker number, size, location, and combination of the lock installed.

Imagine for a moment that this information was stored as function of the Members tabular array. We'd just add a handful of fields to the Members table for that purpose.

Here'southward the challenge with that type of combined data construction. If the facility added a number of new lockers that were not withal assigned to a fellow member, where would that data be stored? Would users create new, blank member records to be able to store the locker details? In addition, if a member left the gild and the member was deleted from the "Members" table, that would also delete the locker information.

In database terminology, these issues are known as an insertion anomaly and deletion anomaly. Many folks new to data structures autumn prey to these problems. It's the reason we desire to track ane and only i kind of information in a table. Nosotros want to avert compound record structures where multiple different people, places, objects, or events are stored in the same table.

In this instance, we'd create two separate tables for Members and Lockers. Since a member is provided one and only one locker, and a locker is assigned to just ane fellow member, we accept a one-to-one human relationship.

Why Utilize a One-to-1 Relationship

Nosotros've given two examples of one-to-one relationships. In one example, it was to minimize information storage and provide separate security measures. In the other, we wanted to create a data model that allowed for the cosmos of data that was truly divide and unique.

Nosotros desire to advise, however, that it's like shooting fish in a barrel to overuse these relationships. Be sure not to implement them where they are non needed and non to make an overly complex ERD where one is not needed.

For example, according to very strict readings of data normalization rules one might feel a 1-to-i relationship is needed. But also recall that many of these very stringent rules were created in a day and time when computer storage was extremely expensive. The amount of storage nosotros take on our electric current mobile phones would accept price tens of millions of dollars 50 years agone.

One of the normalization rules says that an aspect (field) should merely belong to a table if there's a value for each and every instance (tape). We know that most people accept a home telephone number, just there are exceptions. Under strict interpretation, we would place the home phone number in a separate one-to-one file.

We recall that'southward overkill in this twenty-four hour period and historic period. Lack of a value tin can convey meaning too. In an odd sense having no value tells us that it does not exist. Or we don't know the value equally nonetheless.

For example, the lack of a home phone number could tell united states that this person does not have a home number. That'due south becoming more and more common with ubiquitous mobile phones. Or it could tell us that nosotros merely do not have the data yet.

Be prudent in your utilize of 1-to-i relationships. They exercise take value and are of import under some circumstances. Apply them wisely and carefully where applicable. But don't get carried away.

Many-to-Many Relationships

As nosotros mentioned previously, many-to-many relationships are quite common in database blueprint. We frequently find them simply after discussions with organisation users and management. In many cases, they are not obvious until use cases are defined, and business needs investigated.

Some many-to-many relationships are obvious. There's a classic case nosotros apply in pedagogy relationships that involves Students and Classes.

Intuitively we know that a Educatee generally will have many Classes. And a Class volition likely accept many Students. Thus a many-to-many, as shown below.

The question becomes, how exercise we implement a many-to-many relationship in our Honeycode systems?

Many developers first attempt putting a foreign key field in both Students and Classes tables to solve this data need. It makes sense to new folks since we used a foreign key to resolve a one-to-many relationship. However, that won't work for a variety of reasons.

Let's visualize the problem for a moment. Nosotros have a table of Students and a table of Classes, equally shown below:

many-to-many relationship
Imagine this information is printed on a big slice of newspaper and hung on the wall. The school registrar would like to run across a diagram that demonstrates which Students are enrolled in which Classes.

I think most would do so by drawing lines between the Classes and Students. By post-obit the lines, either style, nosotros could see who is linked to each Class, or which Classes each Student is enrolled, as follows:

Although a fleck messy and hard to follow this series of lines accurately describes the many-to-many relationship betwixt the entities. Each Student tin can be enrolled in 1 or more Classes. Each Form can accept i or more than Students enrolled. Just follow the lines from either side. Each line represents one Student enrolled in ane Course. Count up the lines, and you know how many class enrollments be.

And then how practise we represent those lines in information? The answer is straightforward. When we have a many-to-many human relationship, we add a third join tabular array in the middle to rails all these links. In the above case, nosotros'd probable accept a table called Enrollments. In essence, we're converting the lines in the diagram to records and data.

many-to-many relationship Notice the many-to-many human relationship has been converted into a pair of one-to-many relationships to our new entity Enrollments. Farther, detect that Enrollments is the "many" side of both relationships with two crow's feet attached to it. As discussed in the previous commodity, the crow's foot designates the child side of a relationship. Enrollments is a child of both Students and Classes, and thus Enrollments will have two foreign keys, one each for Students and Classes.

We described that in Enrollments, we'd convert the lines from the diagram above to data. Each line starts at a student record and ends at a grade record. Each row in the Students table has a unique Student ID, and Classes take a unique Course ID. Each line thus becomes defined by the starting Student ID and ending Course ID. Each enrollment would deport the ID of its parent records, both Students and Classes, as shown below.

many-to-many relationship We've left the lines between Students and Classes to brand information technology easy to run into how each line translates into a row in the table Enrollments. Observe also that the Enrollments table tin have other attributes. We accept a Class Course aspect. If we call up near information technology rationally, a class is not an aspect of a Student, nor a Course. It's a characteristic of a Student in a Grade and thus belongs on Enrollments. We could imagine other attributes like instructor'southward notes, number of absences, or tuition cost for each pupil.

In Honeycode, nosotros would create all 3 tables as shown to a higher place. As discussed earlier, Rowlinks in Honeycode are created in the child table to the parent table. In this case, we'd create ane Honeycode Rowlink from Enrollments to Students, and another Honeycode Rowlink from Enrollments to Classes.

I other detail to notice, each row in the Enrollments table also has a primary key aspect called Enrollment ID. Chief keys are used to link a child record to parent records, and you may wonder if we need a primary fundamental in Enrollments every bit it has no related child table. Best practise shows that we should have a principal primal for rows in all tables. In that location are other more than advanced uses of primary keys that are beyond the scope of this commodity. There are times when nosotros'll need to place the unique ID of a row during automation or integration scenarios.

The great news for us is that Honeycode takes intendance of generating primary keys automatically and invisibly. It's in that location even if y'all don't encounter information technology. And it'due south really beneficial under some key areas of functionality.

When a Ane-to-Many turns into a Many-to-Many

There are mutual use case patterns we see that can alter the type of relationship used in system design. What follows are some examples.

The key to building a successful ERD and Honeycode system is communication. Nosotros tin can't help solve an automation need without fully exploring and understanding how a business operates.

In many ways building a custom system is like a tailor making a custom adjust. It starts with extensive measurements, choice of jacket and pant way, materials to use, and more than. The tailor designs the suit around our body mode, much like custom software is built effectually a business's needs and operations.

Let'southward presume we're building a relatively simple order entry system where customers can identify orders for goods from our website. Each customer tin take multiple aircraft addresses attached to their account, and they choose ane of their aircraft addresses for that order during checkout. In this case, nosotros'd have an ERD that contains a 1-to-many relationship between the Shipping Addresses entity and the Orders entity. An Order ships to 1 and only one address, simply a Aircraft Address may exist linked to many different Orders.

one-to-many relationship However, in further discussions with the client, it becomes apparent that sometimes customers ship orders to more than than one address. For instance, a customer may social club a number of similar floral arrangements as holiday gifts and take each of them shipped to a different address. In this case, an Order can take more than one Shipping Address, and a Shipping Address can be used on more than than ane lodge. Then our human relationship becomes a many-to-many, every bit shown below.

many-to-many relationship First and foremost, this change to a many-to-many is more complicated than we've shown here. This change requires a fair amount of reorganization of the ERD. There are rather sweeping ripple effects required for an atypical merely important utilise case.

That's why we want to get an ERD correct the first fourth dimension. Sometimes changes to an ERD are relatively simple. Only some crave substantial change. If nosotros've already built a business system in Honeycode, we'd be making some challenging development changes.

These are oftentimes referred to as fringe or edge cases. These are events that don't happen often but still need to be considered. Nosotros simply observe the fringe cases through a thorough analysis of a system and repeated conversations with both users and management. Oftentimes users are more enlightened of these fringe cases because they are on the forepart line dealing with customers and their questions and issues.

Every bit with all relationship decisions, nosotros should examine each change carefully for necessity. It'due south possible to turn a i-to-many into a many-to-many when not strictly needed. If there are fringe or edge cases that happen very rarely, nosotros might expect for a workaround for those circumstances.

Assume we only had one instance a yr of customers requiring multiple shipping addresses. It would exist reasonable to crave that customer to place separate orders instead, each with a singular aircraft address. Information technology'southward less than ideal, but in software development, we need to weigh the incremental cost and complexity of a change with the derived benefit.

Wrapping Up

Delight think that creating an ERD is creating a roadmap for the construction of your database organisation. Later additions or modifications can exist easy or very challenging and require lots of effort. It'southward best to get it correct the first time.

There are a lot of similarities between designing a home and designing a database. Imagine everyone like-minded on a habitation blueprint program. The builders create a home that is exactly as specified on the plans. When the client walks in for the offset fourth dimension, they ask for a few pocket-size changes.

First, they'd like the color of the bedrooms to be changed. That's alike to making a alter to the user interface of a system and generally relatively easy to accomplish. But then the client realizes that the kitchen does not become the morning sun, so they'd like the kitchen moved from the west side of the home to the eastward side. Now I think everyone realizes that a massive alter requires lots of deconstruction and reconstruction. Oh, for good measure, the client decides to push the foundation out another 4 feet in each direction. At that signal, it might exist easier to tear downward the existing construction and build from scratch. And, yes, that happens in the building of database systems too. Sometimes clients tin't actually envision what they're asking for, but they know information technology'due south not what they want when they see it.

Fortunately, Honeycode is a no-code tool. From personal experience, we can say that significant changes are simpler in no-code environments than in low- and pro- lawmaking platforms. Instead of writing code to change the order of objects on a course, we can click and elevate them. It's easy to create new forms to have the identify of existing ones. And considering Honeycode takes care of a lot of the relational linking for us backside the scenes, our piece of work is lessened when changes are needed.

Regardless of the complexity of your systems or the tools yous apply to build them, delight exist certain to take the fourth dimension to program before developing anything concrete.

If you would like to learn more than about Amazon Honeycode and how to create useful custom business solutions, experience free to contact u.s.a..

Keep me posted