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