4

I am integrating employee/position data with another system.

The first system is HR. It has an Employee table and a Positions table. The second system tracks office space and tracks who the office belongs to. It uses one table [em] to store both employees and position data.

If an employee changes position then the position id changes. If the position becomes vacant and a new employee fills the position then employee id is changed.

Both position id and employee ID act like primary keys for this table. I am trying to figure out if there is a name for this type of relationship in a database terminology?

2

It's called a composite key.

It doesn't matter how you use a composite key. If two pieces of data uniquely identify a row, it's called a composite key. In fact, the use case you're defining is almost always the use case of a composite key.

They're usually natural keys (having both fields related to the data), rather than surrogate keys (having both fields independent and abstract from the data). A store location, and product ID is the canonical example of a natural composite key. You're just using employees.

If, by chance, you mean to ask if both inputs used in the composite key have a name when they have a UNIQUE property, then no.. You can do it, but even in your case it's a bit awkward right? I mean, what if one employee is managing two spaces?

CREATE TABLE em ( emid serial PRIMARY KEY, name text, position text );
CREATE TABLE space ( spaceid serial PRIMARY KEY, owner text );
CREATE TABLE em_space (
  emid    int REFERENCES em,
  spaceid int REFERENCES space,
  PRIMARY KEY (emid, spaceid)
);
2

If there two different columns, each of which by itself could be used as a primary key for the table, we say there are two candidate keys.

One candidate key is chosen as the primary key. The other candidate keys which were not chosen can be referred to as alternate keys.

Any key can be a single column or a combination of columns. Keys made from more than one column are called composite keys. A column may be part of more than one key. For example candidate keys may be (col_a, col_b), (col_a, col_c), (col_d); col_a is in more than one candidate key. The first two of these are composite candidate keys, the last is not.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.