Composite primary key or primary key plus single index?

Question

I am developing a report in my system to control the productivity of teams, the table of teams has the following information:

(tblequipe)
| id (pk) | nome | meta |
|    1    | EQ01 |  5   |
|    2    | EQ02 |  7   |
|    3    | EQ03 |  6   |

One problem I found was to display the productivity of teams when data did not exist, for example, how I will consider the team’s productivity on 01/01 if there is no release (this release is in a table that contains the raw data, that is, several records that will be summed giving the total amount executed on a certain day) on that date? So I thought I’d create these two tables:

(tblmapa)
| data  | id_equipe | meta |
| 01/01 |     1     |   5  |
| 02/01 |     1     |   5  |
    .         .         .
    .         .         .
    .         .         .
| 31/01 |     1     |   5  |

---------------------------------

(tblprodutividade)
| data  | id_equipe | executado |
| 01/01 |     1     |     9     |
| 05/01 |     1     |     3     |
| 06/01 |     1     |     5     |

Done this would be able to recover the data with this select:

SELECT
    m.data,
    m.id_equipe,
    m.meta,
    isnull(p.executado, 0) executado
FROM
    tblmapa m
    left join tblprodutividade p on (p.data = m.data and p.id_equipe = m.id_equipe)

My question is about tblmapa and the tblprodutividade, would like to know if it is more appropriate to create a column id in each of them as Identity Primary key and define the columns data and id_equipe as Unic or maybe just create the columns data and id_equipe as Primary key composed.

Additional
I would like an answer that takes into account the performance and storage space of Primari key + Unic or Primary key composed, the columns being of the type date and int respectively.

Related question
When the use of composite primary key is recommended?

Answer 1

Tables for records that have identity will always have a natural primary key.

Are you trying to decide whether to use this natural primary key as the table’s physical primary key or to use a surrogate key as a physical primary key and ensure the integrity of the natural primary key through a single index.

The two approaches are valid but the rarely decision factor (think never) is storage space or performance.

Concern about performance is not important because:

A search by Primary key clustered can improve performance.

But you can choose both the key Primary and any other index like clustered (only one of them, of course, but it could be any one of them).

So if you are going to use key surrage (include the ID column), just create this non-marketed key Primary, and make clustered the unique index representing the natural primary key.

So, either of the two options will have the same performance just by creating the indexes properly.

Concern for space is not important because:

If you use surrogate key, in fact you will consume more space because beyond the index created by the bank for this physical key Primary you will still have to create one more index for the natural key Primary. But the space occupied by an extra index should not be your first concern.

Unless you intend to achieve many millions or billions of records in the coming years, performance and space is the last concern. Even if you are going to pay for GB of storage (for example, cloud hosting), the space occupied by the index will not be as relevant as other database factors.

Completion

The decision factor is the design of the application that will consume this database. Using surrogate key simplifies the use of Orms and decreases the need for complex database Refactoring in case the natural primary key changes (the natural primary key may change due to new business requirements).

Given the requirements you have set out, do not include the ID column as you are not using it for anything or suffering from the problems I have described (ORM and Refactoring).