A
Your database is badly designed, a very bad programming practice is to use composite keys and worse is to use it in a relationship many to many. For that table where you have the composite key creates a unique with the composite key fields, remove the composite key and create a key with a id whole or long that self-incredizes only for that board. That's just in the design of the bd, once doing that truthful as your problems start to solve in hibernate.Is it a bad practice of composite keys?
Answer: https://inmensia.com/blog/20070223/elegir_clave_primaria_tabla_base_datos_oracle.html I quote:Sometimes I have the impression that choosing the columns that will constitute
the primary key to a table is a decision that is usually made very
the light, when reality is that the correct choice of
primary keys is a decisive factor in building a model
well-formed, coherent, maintained and
easily expandable. The most ironic thing about it is actually
is a very simple task if you understand that no value taken
the domain we are trying to model serves to identify
uniquely to database records. In other words, not
a column containing
information that has meaning for users; you have to create a
column expressly for the primary key. Or a third
form, if you have to create a table of people do not use DNI as
primary key.But why isn't it a good idea to choose DNI as a primary key?,
Does that number not identify us in a unique way? Yes, but
Absolutely NOT. Consider, for example, minors
citizens of other countries do not normally have a card
identity, and all of to mention further the fact that
DNI numbers are not unique, historically the same numbering is
has reused in different people.Maybe right now I'm thinking of alternatives very
smart to try to solve the problems that arise in
the previous paragraph, possibly by incorporating new
columns to the primary key, but believe me, leave it, the effort will be
the reality is that the problems will not disappear, they will
They'll increase. For example, we can think that we can store
minors in our table if we add a new column to the
primary key to tell us if the DNI is actually yours or yours
legal representative. But it happens that a person may have several
children, so we would need another new column for
distinguish them. We can also think that we can store people
of different countries if we add a new column that tells us
the nationality of each person, thus allowing the same
number is repeated for people from different countries. But it happens that
a person may have several nationalities. And so, back
Start. The normal thing is that following this line of reasoning is
I end up needing 7 or 8 more columns. General lines
primary composite keys should not be used in tables
teachers, that is, keys formed by several columns in the tables
that store the basic entities that manage a system. Complication
models making them difficult to maintain and expand. Think of it.
previous example, if we persevere in our original idea of
use DNI as a primary key, so that tomorrow morning
We found a problem with the ones mentioned above, and we also found it
we solved by adding a new field to the primary key. Well,
It would happen that we had to add that new field to all the tables.
and processes that referred to the table of people. The change
It wouldn't be unique and localized, otherwise it would expand like a
contagious disease by all entities and processes of the system
that had some kind of relationship with the people table.Be careful with the composite keys, especially because
sometimes camouflaged under the appearance of what normally
are called "smart codes". A smart code is the union
of several keys in a single column, that is, a primary key
composed with lamb skin. A typical example of this type
code are the locators, like a cinema entry in which
figure “S07-F12-B08”, meaning “Psalm 7” (S07) “Place
12" (F12) "Butaca 8" (B08). This type of composition is used to
times to identify entities by location, assuming the
the fact that there can only be one entity in one place to the same
Time. The main problem posed by this type of primary key
it's that sometimes, in the real world, entities get lost and end
in locations other than those that indicate their codes, being
need to change the primary keys in the main table and in
all tables in which they are referenced, which can be
rather laborious, not to say cumbersome and prone to mistakes.The previous paragraph gives us the last two final clues for
understand why you don't have to use DNI as the primary key to
People's board. The first clue is that a primary key never
can depend on information entered manually. If it's done
depend on the value of a primary key to what a user writes
a brutal unit is being created with the operation
internal system. The coupling between layers of an application
must always be weak, so that each can be modified
individual component without affecting the rest, and
consequence, the rules governing the real world do not have to
influence those established within an application. Yeah.
the primary key to the person table was DNI, and tomorrow is
decide to use the NIF, then you should change the whole
model and processes to manage the key as a field
alphanumeric instead of just nunical. If DNI wasn't key
Then it would suffice to change that concrete field in isolation.The second clue is that a primary key will never be able
change. The primary key to a register identifies form
unites an object within the domain that is being modeled. Yeah.
changes the primary key of a registration occurs a "mutation" by
which the record passes to represent another object. This look
Maybe it's a little subtle to understand, especially because it carries
a certain level of abstraction. A person could be said to be the
the sum of its individual characteristics, as well as the place it occupies
in any of the dimensions in which it is projected (and
possibly its absence in the dimensions in which it is not
project). It is an entity, a unique and distinguishable being. Your key
to identify it in a unique way, if changed
would represent another entity, not the original individual. Think about it.
terms of variables, such as those used normally in
any programming language. First they are defined, and then they are defined
can change its content, just as a person can
change clothes or hairstyle. Variables always do
reference to the same specific information, regardless of the
value contained at a given time. People happen.
It doesn't matter the hairstyle or the clothes that we wear, and even where we get
Let's find, above all we remain ourselves, not
others. If a person is changed his DNI because he introduced a
wrong number on high, must remain the same entity as
was originally inserted into the system, not another that implies
a massive update of relationships.In short, the correct way to identify entities in a
table is to use a unique code or identifier that lacks
meaning in the real world. A simple numerical value generated from
sequential form is enough. The idea is to create a table with a
first column of numerical type to serve as primary key. In the
initial example, the people table, could be something like: ID_PERSON
NUMBER(10) NOT NULL. So when you insert a first
person in the table is assigned value 1 as primary key (or
1527, it's indistinct. When inserting a new person is assigned
2 (or 1528). And so on. This way there is no possibility
of conflicts, or what is the same, of encountering errors by
duplicate primary keys. Each person is paired with one
identifier that distinguishes him univomente from the rest. And I said it to
person serves for any kind of entity. A motor is not
identify by its frame number, but by its primary key. A
Television is not identified by its serial number, but by its key
primary. An invoice is not identified by its number, but by its
primary key. A user does not indentify by name, but by
primary key. Get the idea?This way of defining the keys usually creates quite confusion in the
developers not used to this way of modeling. The first
issues that are usually raised are performance issues,
mainly because searches for columns like DNI are quite a few
usual. As you stop being part of the primary key you need to define
Additional indices on tables for these columns. Some
designers spontaneously refuse when it is suggested to add several
indices on the same table claiming that it corresponds to an evil
design. But then what the hell do we want the indices for? Another
usual issue that is usually raised is how to generate the keys
primary. The answer I think depends to a large extent on the
tools used to develop. Oracle for example
provides SEQUENCE-type objects, which generate values
Atom-shaped sequentials. Some managers indicate that
columns are self-incremental, so that every time it is
insert a log is automatically generated a new value. Them
"gurus" tend to wander about aspects such as if necessary
use a single sequence for all system entities or
a different sequence for each table. What's clear is that never
you should run COUNT or MAX on the tables to get the
next sequential, they are direct attacks against integrity and
application performance. Another issue that is usually raised is
the theme of the size required by the column of the primary key. A
size of 10 represents an amount of ten billion values
possible, enough for most purpose applications
General. What needs to be done above all is that all tables
have a primary key column with the same size, not each
with a different size adjusted according to its estimated occupation.
Referring to this last question, we must nuance to add a
column size ten does not imply automatically adding 10 bytes per
registration, the additional space required will depend entirely on
database manager being used.Working with tables defined this way for the first time can
To be a bit disturbing at first. It may seem to keep
the coherence of so many identifiers is complicated, but not
is, in fact, today is the most natural way to design and
work. Simplify the design of the layers of persistence with the
that are managed to map objects in memory, while helping
separating information that is significant for users
the way it is managed internally. If your tools don't
support this way of working I'm seriously thinking about
replace them with new ones.And finally, I didn't want to stop commenting on the fact that surely
all these reasons seem unnecessary complications for
small systems in very controlled environments. However, think
so it is to forget a maximum computer that must always have
present: the requirements always change!
