🥱Second solution ("Medium")
© Alexander Sychev, Sergei Sychev, “TRIZ-RI Group”
Since the solution is weak, the requirement was strengthened to stimulate the applicants imagination: "You must build not a simple, but an ideal model of data organisation", that is, one which does not have to be changed in case new conditions, not considered earlier, appear. After all, you know that this is a standard requirement.
The applicant created the following 5 tables:
1) Table "Agents"
Where the field Agent_id is the primary key.
There can be any number of property fields (all necessary information: address, phone number, ownership, etc.).
1.
Pan Myšlenek Company
...
2.
TRIZ-RI Group
...
...etc.
...
...
N.
Sychev&Co Ltd
...
2) Table "People"
Where the field People_id is the primary key.
There can be any number of property fields (all necessary information: address, phone number, email, etc.)
1.
Alexander Sychev
...
2.
Alevtina Kavtreva
...
...etc.
...
...
N.
Sergei Sychev
3) Table "Agent_People"
Here both fields - Agent_id and People_id – are primary keys.
The field Agent_id is a foreign key linking to the table Agents, and the field People_id is a foreign key linking to the table People.
1
1
3
2
4) Table "Agent_types"
Where the field Type_id is the primary key.
1
Our company
2
Client
... etc. ...
...
n
Supplier
5) Table "Agent_agent_types"
Here both fields – Agent_id and Type_id - are primary keys.
The field Agent_id is a foreign key linking to the table Agents, and field Type_id is a foreign key linking to the table Agent_types.
1
1
3
2
With this approach, all entities ("companies", "people") and their types are described separately from the interrelations. Hence, it is a good approach, because, in case of unrecorded situation, we simply register the new data in the dictionaries, we don't have to reorganize old tables or create new ones.
For example, if we need to register an individual - as a Client, or as a Supplier or even as our own staff - we just register a new type of agent "Individual" and fill the relevant tables, without creating anything new. Furthermore, in this implementation, the term "Firm" has been smartly replaced by a more abstract term "Agent", which allows to consider "company" as a special case, along with other types of agents.
This, of course, is a good thing. And if you look at the project more widely, it becomes clear that the addition of new entities will not require any changes in program code. As they say: "The structure is normalized."
During the interview, the developer was asked why the table "People" and the corresponding interrelations are needed in this case. After all, if an individual is registered as an agent, it is possible to add various types of agents: "Our employee", "Employee of a Customer", "Employee of a Supplier" etc., just like we did with the types of companies "Our company" etc. And leave only 2 tables: "Agents" and "Agent Types" instead of 5.
The developer reasonably answered that "people as people", the "people as agents" and "firms as agents" may have different properties, which must also be stored properly. Imagine that an "Agent" has a specific property "Date of the last transaction" or "Ownership", and "Our colleague" does not have such properties at all. These fields are not needed to describe the employee. Of course, in some particular cases we can possibly somehow unify the properties of different entities, but in general it should be assumed that they are just different.
We leave a bookmark here and move on.
We have discussed the positive sides of the second solution. Now let's discuss the negative ones.
So we are following the logic of this solution. Imagine that we would like to add not just a variation of existing entity ("people", "agents") but an absolutely new entity (with all of its properties), for example, documents. We would have to create a new table "Documents":
1
Contract N 7 ____
...
2
Invoice N 15 _______
...
3
Сertificate N 8 ________
...
as well as the table which stores types of documents:
1.
Contract
2.
Invoice
...etc.
...
N.
Сertificate
Then create interface tables describing the interaction with people and agents. These are the tables:
1
1
3
2
1
1
3
2
The number of M interface tables connecting N entities is calculated as follows:
Hence, if we add K new entities, the number of interface tables will grow according to the formula:
Thus, if initially we had 2 entities, adding the third one will require 2 new interface tables to be created. Adding another – 3 more. Another – 4 more. And 5 more for the next one etc. n-1 for the n-th entity.
And that's not counting the number of tables which describe the entity itself. In the current example, there are two for each. So for this example, the number of tables increases according to formula:
.
So for 12 entities we can end up having 90 tables, which radically reduces comprehensibility and complicates maintenance.
This is an obvious disadvantage. Therefore, method 2 can be called a "compromise". It is clearly better designed than the first one, but the idea has obviously not been thoroughly explored. And the reason for that (back to the "bookmark") is unresolved contradiction:
"The properties of different entities should be different, so we can work with entities according to their features, and should not be different, so the number of tables would not increase.".
This contradiction was not resolved by the author of the second solution.
Last updated