Composite primary key or primary key plus single index?

Question

Composite primary key or primary key plus single index?

Navigation

#1 by (3 votes)

3

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

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

One problem I encountered was when I viewed the productivity of teams when there was no data, for example, how do I consider team productivity on 01/01 if there is no release (this release is in a table that contains the data gross, that is, several records that will be added giving the total amount executed on a certain day) on that date? So I thought about creating 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     |

This could retrieve 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 regarding tblmapa and tblprodutividade , I would like to know if it is more appropriate to create a id column in each of them as identity primary key and set the columns data and id_equipe to unique or maybe just create the columns data and id_equipe as primary key . >

Additional
I would like a response that takes into account the performance and storage space of the primary key unique or primary key , with columns are of type date and int respectively.

1 answer

GlassFish does not get root context of the application Error using required inputs on Primefaces

score 3 · Accepted Answer

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

You are trying to decide whether to use this natural primary key as the table's primary physical key or use a surrogate key as the primary physical key and ensure the integrity of the natural primary key through a unique index.

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

Concern about performance is not important because:

A search for the primary key clustered can improve performance.

But you can choose either the primary key or any other index like clustered (only one of them, of course, but it can be any of them).

So if you are going to use surrage key, just create this nonclustered primary key, and clone the unique index that represents the natural primary key.

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

Concern about space is not important because:

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

Unless you intend to hit many millions or billions of records in the next few years, performance and space is the ultimate concern. Even if you go pay for GB storage (a cloud hosting, for example), the space occupied by the index will not be as relevant as other factors in the database.

Conclusion

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

Given the requirements you've set, do not include the ID column because you're not using it at all and you do not suffer from the problems I've described (ORM and refactoring).