While working with big databases and huge amount of data, it’s always a good idea to create a group of related Libraries with the connected entries. Thus, the information is structured, and you don’t duplicate the same entities in several Libraries.
The are three common relationships that are used in database design: one-to-one, one-to-many and many-to-many. Let’s see the difference using an example of a database for the Gift Shop.
There will be four Libraries in our Gift Shop DB:
- Products with Product ID, Name, Price and the list of Buyers
- Customers with Customer ID and Name
- Transactions with Transaction ID, Purchased product, the Purchase Date/Time and Buyer
- Mailings with the list of customer emails and a checkbox that defines whether this customer is subscribed or not
Now, let’s set the relationships between entries. Each customer may have only one email, that is why Customer ID (from Customers Library) and Email (from Mailings Library) have one-to-one relationship. Each customer can initiate as many transactions as he wants, but in each transaction there’s only one buyer. Thus, Buyer (from Transactions Library) and Customer ID (from Customers Library) have one-to-many relationship. And finally, a customer can buy several products, and one product can be purchased by different users. This is many-to-many relationship.
So, Customers Library that initially had only two fields — ID and Name — now has links to data from other Libraries with the list of all purchased products, the list of all transactions and email address:
To create the relationship in Memento Database Desktop, you need Link to entry field type. Click from the toolbar and choose the relevant option from the list. Then, you need to adjust:
- Properties by choosing the related Library, the Type of relationship and whether this relation is Required or not
- Attributes — the additional properties of the fields that are going to be related. Going to the example above, it will be a good idea to add Boolean Attribute “Purchased” for the Customers-Mailings relationship. If a customer has already purchased something, the flag will be set to True. Then, you will be able to distinguish the actual buyers from those who have just registered in the shop and send different mailings to both groups.
- Dependencies — the conditions that define whether this relation will be visible or not
If you link Libraries, you might find the Lookup field type useful. After adding a Linked field, you can display additional information about the entry from the linked library with this field. Here is what you can customize within the Lookup field type:
- Set the Field name to define how it shows up in the Library
- Set Lookup parameters: choose the Library and the Field there to get the information from
- Set how to Display this field in the Library
For example, you have a library with recipes that you cook, and you wish to display the type of wine that goes well with a certain dish. At the same time, you have a library with various wine types. You would need to add the Link to Entry field in the recipes library to the wine library, then add the Lookup field with Lookup Parameter > Field > Wine type. After that, when you add the wine to your recipe, the type will show up automatically in the recipes library entry.