Reference Tables In Er Diagram

Table references and relations

Table references – also known as relationships or links – are one of the most important features of Ninox. The ability to create relations between tables differentiates Ninox from many other apps which only allow the adminstration of simple lists. In addition, table references are one of the main differences between real databases and spreadsheet programs.

If you don't have a lot of experience with database systems, you should read this chapter to learn which different types of relations exist and how to use them effectively.

Basics: The 1:N Relation

Ninox supports – as most relational database systems do – 1:N-relations as a foundation. That means that one data record from one table is assigned (related) to many data records from another table.

A good example are clients and invoices. You can assign as many invoices as necessary to one client. However, for each invoice there can be only one client assigned.

We would like to introduce you to this topic also with example data on Ninox. Inside the database Data model, relations, constraints are two important tables: Client and Invoice with the following data model and including these values:

Data model	Client	Invoice

You define this fundamental relationship by creating a table reference from the table Invoice to the table Client: The invoice is linked to one client.

You need to do this step by the following way:

Go to the Invoicetable form view.
Activate the Administration mode.
Click on one field into the table and select Link to from the following menu which should appear.
Please select Client and now both tables are related with each other and the data model now should look like this:

If you go to the form view of Invoice now, you will find only one field to enter / select the client. Switch to the form Client

and you will see an embedded table of all the invoices related to this client. Just to give you an idea how the result can look like we assigned zero invoices to Martin, one to Philipp, two to Steven and four to Thomas. As a result we get different numbers of invoices for each client, but every invoice has just one client. Two examples from the Client table:

We assigned zero invoices to Martin, so there are no Invoice numbers related to him.

We decided in advance that Thomas is linked to four invoices and now we have four different Invoice numbers linked to him.

On the other hand, for every Invoice number we have just one client record which looks like this into the table with form view:

Subtables: The Composition Relationship

A special form of the 1:N-relationship is the composition, where composition means something like 'consists of'. A good example for this case are invoice items. An invoice consists of several items. If the invoice is deleted, the invoice items become obsolete.

You can define a table reference as a composition relationship (also retroactively) by setting Composition to Yes under More options in the field attributes.

The consequences are:

In the data model the table is displayed as a subtable of the referenced table (supertable) – integrated entirely.
On the database homepage the subtable can't be found anymore.
By deleting a data record in the supertable, all related data records in the subtable are eliminated.
The form of the supertable does not provide the operation Search and add (magnifier icon) for the subtable.
The form of the subtable does not provide the operation remove reference.

Subtables are good for elements that are linked together closely, for example:

Invoice items belong unequivocally to one invoice.
A telephone number is firmly linked to a contact. If the contact is deleted, the telephone number should be deleted as well – it is of no use without the contact information.

Contrary examples:

The employees of a company shouldn't be listed in a subtable, because they exist independently of the company: they might change their employer.
Invoices for one costumer
An invoice item is – as seen above – a subtable of an invoice. It should have a normal relationship with the article (product, service), though.

Relationship Tables: the N:M-Relationship

Sometimes 1:N-relationships or compositions, respectively, don't suffice to represent the circumstances of the case. Like most relational database systems, Ninox doesn't support N:M-relations directly. However, real N:M-relationships should be the exception; in most cases that kind of relationship will be qualified further, that is, enriched with more data.

An example: In the table Firm we are entering firms. Another table Person gathers contact information of individuals. Now we want to assign which person works for which firm. The simplest model would be a reference from Person to Firm. However, if you want to include the fact that one person can have several employers or change their employer over time, this model is not sufficient.

In that case you need a third table, for example Employee. At the creation of that table, you define two references: one to Firm

and another to Person. Both relationships should be marked as Composition, because, if the firm is deleted, it doesn't have employees anymore; and if a person is deleted, they are no employee any longer.

Notes:

Although Employeehas got two composition relationships, the table is not displayed as a subtable. Obviously, Employee can't be the subtable to two different supertables.
Keep in mind the consequences of a composition relationship to the deletion of data records: if a person is deleted, the employee will be deleted, but not the firm. If a firm is deleted, all its employees will be deleted. The persons stay untouched, though.

The table Employee is a so-called relationship table, because it models the relationship between persons and firms. In addition to this main purpose, you can use this table to enrich the relationship with valuable information. Examples would be fields for the entrance and exit date or the salary. This converts the table into a display of the working condition or even the employment contract.

Relationship Networks

The three basic kinds of relationships can be expanded in any way. There are no boundaries to creating higher-grade relationships – for example between three tables.

It might be helpful, if you outline your database on paper first.

First draw all the objects you want to manage as boxes. Each box represents one table. The next step is to think which relationships exist between these objects. 1:N-relationships can be drawn as an arrow. If that doesn't suffice, you must include another box as the relationship table.

Take care that you don't create redundant (unnecessary) relationships. Follow the arrows: If you can get from box A to box B on two different ways, you should rethink whether those two paths actually represent different issues. If not, you can simplify here.

At last, some advice: don't try to cover all possibilities. Sometimes it is better to use a more simple model and to indicate potential exceptions applying comments or other tools. KISS – Keep It Simple, Stupid!

Constraints

Sometimes datasets which are related with each other can be very large and it can be very helpful to restrict working steps. For example you can reference tables like Employee and Company.

Company	Employee

If you referenced them like described in the paragraph above, you can assign a company to an employee through the following dialogue which is reached through klicking on your desired employee.

One possible constraint could be that it is just allowed to select companies located in the same country than the employee.

Therefore you need to click on Actions and select Edit fields. Please choose your table reference which should be restricted which is Companies in this case.

Please click on More Options and go to the Constraints field. Enter your desired restriction via the visual or the text view. For this example one way to solve the problem would be the following code:

a.Country = b.Country

Please note that your constraint is just valid for future selections from your table reference. Requests from the past remain unaffected.

Drag & Drop can be very helpful to figure out which table has which letter in front of the column title.

Source: https://ninox.com/en/manual/tables/table-references-and-relations

Posted by: rafaelaprzybylskie0193323.blogspot.com