Two key attributes in an EER, what does it mean

primary-key

I'm currently drawing an EER diagram and stumbled across this problem:

Let's say I'm modeling a workplace where all employee's uniquely can be identified by their name (ie no employees share the exact same name) and their employee number. Is this a correct way to model it?

I'm not really sure on whether this is an ok design. Does this design say that Name and Emp_nr together form a unique Employee, or does it say that Name and Emp_nr independently from each other can identify my Employee? I've been searching everywhere and couldn't find a single example on where 2 key attributes is used.

Best Answer

This only tells that an Employee has two attributes which are bith a combined primary key

he has a name and
a employer number

In a Table structure , it will be displayer as two columns for the Table Employee and the would have a PRIMARY KEY(Emp_nr,Name)

Employee
-------
Emp_nr

Name

Related Solutions

Primary key should be chosen so as attributes never change

Keys are identifiers. They permit database users to identify the specific things about which information is being recorded in a database. If the values of key attributes are subject to change then there may be a risk of misidentification, particularly if the identifying information is also recorded elsewhere outside the database (e.g. in other databases, documents or in the mind of the users).

Sensible criteria to consider when choosing what keys to use are: Stability, Simplicity and Familiarity. These are good criteria for choosing any key, not just "primary" keys (the designation of a "primary" key is essentially arbitrary and a matter of convention or convenience - it doesn't mean those keys are fundamentally different to any other).

Stability, simplicity and familiarity are of course all subjective and relative criteria. Any attribute, whether key or not, might need to change and sometimes the ability to change a key attribute is a necessary part of maintaining accurate data and the correct operation of the systems that depend on the data. Very often there is no way to foresee a future requirement that may require such a change. It isn't necessary or even achievable to expect all keys never to change.

Uniqueness isn't really the issue because uniqueness needs to be enforced whether key values change or not. The uniqueness is implicit in the fact that it is a key and is maintained as such by the DBMS.

Mysql – Two primary keys to one foreign key

I would suggest that you either want a single "couple" table instead of two, if the properties of each coupling are sufficiently similar, or a couple table that the two existing types of couple "inherit" from, like so:

RC                  Couple                                Egg
----------          ---------------------------           ----------------
C ID (FK)   ----->  C ID (PK)                    <-----   C ID (FK)
(rc props)    |     (generic couple properties)           (egg properties)
              |
BC            |
----------    |
C ID (FK)   --'
(BC props)

You could still have breeding and racing details for any could in this arrangement (which might actually be fine, of course) but each egg can only be associated with one couple. You won't have problems with overlapping ID ranges as there is only one range of IDs for couples when modeled this way, and all other tables with data relating to couples only have the one FK linking to the could record.

Best Answer

Related Solutions

Primary key should be chosen so as attributes never change

Mysql – Two primary keys to one foreign key

Related Question