Relations

Records of different tables can be connected with each other: TeamDesk allows a user to relate records of one table to records of another table.

The system presupposes two main types of relationship between records: one-to-many relation and many-to-many relation.

One-to-Many relation

Let’s assume that in your database you created two tables. The first is called Projects, it comprises information about projects. The second isClientswhere clients’ profiles are stored. Every project is ordered by a client; some clients order many projects, while each project can be related to only one client. To linkProjects and Clients, create a relation between these tables. Records are related by their key columns’ values. By default, the Id column is set as the key column. The relation allows selection of a client record (Client Id value) for each project record (Project Id value). As a result clients’ names will be displayed in the Projects table.

What is the Id column? Where it comes from? TeamDesk requires every record in the table to be identified with some unique value. In some designs the record could be identified by some code, for example, a book record could be identified by its ISBN number, a company - by its stock ticker. In our case the client name could not be used for identification and we could simply let TeamDesk assign some incremental numbers to the records - the Id column is created with every table and used as the key by default.

Many-to-Many relation

For example, there are three tables: Designers, Hours Worked, Monthly Payments. Designers table keeps designers’ profiles. Hours Worked table keeps a log of hours worked by each designer. In the third Monthly Payments table designers’ hourly rates for each month are stored. In this case the Many-to-Many relation between the Hours Worked table and the Monthly Payments table can be created. As a result a summary column calculating total hours worked during each month by every designer can be calculated in the Monthly Payments table.