Several tables
There are two ways to solve this with several tables.
Related tables
There are also two ways to organize this way. This is the way normalized.
Don’t forget that if the employee changes roles, you will have to delete data from one table and create data in another. These function tables should only be directly linked to the employee table. Except in a very specific case, they should not be used in other parts of the application directly.
Inheritance
If you want to strictly establish the inheritance adopted in the classes, you will have to have a staff table. And it should have a table for each new descending class to create. This makes everything organized, it’s easy to extend the application with new employee roles, and it’s a little easier to fill classes and save their data into the database.
Just be careful not to hurt the YAGNI or the KISS.
If you take inheritance seriously and do not want to have members of a derived class that are duplicated into another derivative without being inherited, then it is likely that you will need to create a class hierarchy to structure all members properly. This can become confusing, difficult to manipulate and is considered an error by almost all developers.
It gets complicated if you have to do something like this:
Pessoa
Fisica
Funcionario
Comercial
Gerente
Atendente
Vendedor
Principal
Auxiliar
PosVenda
Administrativo
Producao
Cliente
Juridica
Fornecedor
Banco
I made the hierarchy so simplified, could have more levels and obviously much more derived in each level.
But then will make the inheritance more or less (half-mouth)? Will you solve the main one and then resolve these other cases of grouping members in another way? That is, either it will have certain identical members that will exist in different derived classes without having a common ancestor, or it will have these members in the class Funcionario
but will not be used by all derivatives?
Of course, the inheritance of these things can be obtained by interfaces (it’s easy in C# with properties, in Java 8 with standard methods or C++ with multiple inheritance), which would make it a little easier, but it would still be a bit confusing if you had many members like that. And the interface is not a solution for everything. Creating an interface without having a reason beyond the pure and simple grouping, can be an exaggeration.
So, are you going to make the inheritance in full or in half? Perhaps it is better to make the composition.
Relationship (composition)
Unless you use one object-oriented database (do not usually recommend), tables compose the objects and do not inherit them. So it may be more interesting to make a composition and not an inheritance in their tables. In a relational model objects are composed. When you try to have a relational database and an object-oriented model in the application you have a impedance.
There are two ways you can do the composition.
You can have a reference to the employee data in the function classes/tables and use the functions as a binding element in operations such as the sale you spoke about. There’s an answer showing how it does this. But I think it’s wrong. What happens if the employee changes roles? Do you have to change all the places where it was used? Who sold it was the seller or the employee? Well, there are cases that might even make sense to use the function. You have to analyze what you need.
Or you can compose an employee with a role (or more than one, eventually, depending on what you need). Note that this function, if done correctly, can be changed whenever needed. And this way allows working with the employee as a binding element in the sale.
Interestingly if it can only have one function at a time, using the employee makes a lot of sense. If it can have other functions and some of them can exist parallel to others but are disabled for use, it may be that keeping the function is interesting.
The question that needs to be asked is: the function has an official or the official has a function ()? If you answer right, you know what to choose.
Tell me how you’re going to use inheritance for an employee to accumulate functions! This is a classic mistake that people make when they close their eyes and think inheritance is everything.
Inheritance must be used when an object is a (is-a) another object with particularities. A manager is a employee, right? Or does an employee, at a given time, have the characteristics of a manager? He is a manager or he is a manager? In English it is more difficult to resolve this question :P
On the other hand a sale should have only sellers? Then who knows might be right to have the hierarchy or at least the composition in the function. That is, you will always put a salesman in charge of the sale. And eventually you can put his manager as co-responsibility for the sale. And this link must be made only with these two functions respectively. It cannot be any official. In this case an employee may no longer be a seller but there will still have to be an object for this to keep the whole model consistent, since sales were made with this seller. This is good or bad for you?
Think of it differently. If an employee leaves the company, he cannot be removed because he has several other objects that depend on him, but he is not an employee, you change him to "former employee" (and then you would have to have a base class for these two forms: employee and former employee) or kill him and have a property showing that he is no longer active?
And if a salesman works that long, he stops being a salesman and then he goes back to being a salesman. Should the two phases be considered different functions or should it be the same? All this helps define how it should be modeled.
Note that here I’m only talking about the composition, no matter who composes what, is not inheritance.
Separation of duties
This is another very adopted alternative in the past. Actually still very much used today because people learn the old way. It does not mean that this way is wrong and should not be adopted. It has its advantages... and obviously disadvantages.
In this template you have function tables that have all the employee data. So you avoid the composition, the inheritance, no problems with columns that should be null. No problems to create new functions.
Of course, the way to extend the application with new functions will be different. It is clear that in the sale or other locations you will have to say that it is the employee and somehow identify his function to know which table to fetch your data, either by a code pattern or by an explicit information.
If you have to change something in the model that is valid for all functions, you have to do this in all tables. And if you follow the same model in the classes, you have to do the same in the classes.
This may sound terrible, but it is not at all bad. If you consider that everyone commands to apply certain principles in OOP and one of them is the open-close, can be worse to change a class that is inherited than a class that will not be derived. Then we start to see that OOP has its flaws there.
A table
In the above form with multiple tables can have a loss of performance. Virtually every time you want employee information you will have to read two separate tables to get the general employee data and the job specific data.
To solve this you can assemble a single table of employees and in it have the data of all possible functions, leaving without data those that do not matter for the other functions of that employee.
This form has the problem of having a lot of columns that will never be filled, a lot of null
. I’d have to take care of these things.
In addition it will be more difficult to carry the data from and to the database if it maintains the inheritance structure.
This form is not normalized but some cases it is better.
Depending on the case, do not rule out the possibility of making a single table and making the classes reflect this. Inheritance is beautiful on paper, but it’s not always the best solution in real applications. The same goes for normalization.
But this makes it difficult to create new functions because you would have to modify the database table to include the specific information. But you need to know whether this will be common or not. The problem is not so much changing the table, but the way to change the application can become more difficult if you make a "linguing".
On the other hand, the structure of inheritance or composition makes the application more complex. What difficulty do you want to deal with?
On sale
In the sale you will always keep the employee because it is always he who makes the sale, no matter his function.
Eventually you can have more than one person responsible for the sale. And then your application will decide how many can, and what roles they can have. It may be for example that you are a salesman and a manager always, or it may be any functions, or just sellers. Even if it is only possible to place seller(s), could still refer to a manager if there is this hierarchical information in the employee register.
When you have different information, you have to treat it differently. That’s what I said above, you will need to know where to fetch additional data. You certainly have employee role information in this table. So it’s easy to identify where to look.
If it adopts the decoupling itself, either by inheritance or by composition (that better decoupling and keeps more cohesive), to maintain consistency, the screens should be made in the same way.
Now think about this: in the application, how will you keep the person responsible for the sale? Probably the function, right?
If you do inheritance in the application (in the relational table does not give), will you use what in the entire application? The employee or the function (manager, seller, etc.)? There are cases for everything. When only importing the employees' parts, it will deal directly with itself that the concrete object is a manager or a seller. But in most cases it will deal with the derived classes with the functions. This can become confusing with respect to the database. If you know what you are doing, if you organize it correctly, you will have no problems. But if you make a mistake, you will be lost because of the impedance already mentioned.
Polymorphism can help or hinder this case.
Completion
What are you doing this for?
If it is learning to do everything object-oriented, follow this way, understand how to solve the relationship problem and the inheritance or prefer to make the composition that is still object-oriented.
If it’s a real application, think about what it might have in the future. Think about the ease of maintenance.
In applications that tend to be complex, which may have a lot of maintenance, have new functions added, it may be interesting to use the form of inheritance or composition and related tables.
In simple applications, which will be more or less static, which need low cost, ease of understanding of everything, having speed of access to data, doing something simpler is better, even if it is a design bad from a theoretical point of view.
The only way to know what’s right for each case is to have experience. Experienced people can help you but only if they know the whole situation deeply and are committed to doing the right thing. And I’m talking about quality experience, not quantity. There are people in their 30s doing this and doing it all wrong. Asking random people on the internet can give you general information to help you, but it won’t be the ultimate solution to your case.
If you set the class in a way, why should the table be different? You can, but you need to know why. And know the consequences of this.
One thing I learned when I was going to change something in the application and I have difficulty is that the initial modeling of objects was wrong. It’s very difficult to model right, to think of everything, but you have to do it right to facilitate maintenance, you have to understand the problem correctly, even the experienced ones don’t usually do this. I still make mistakes today after years of doing this, but not again after learning something new. This is the most important thing.
And you have to set aside the right way in some cases if you have other concerns like simplification or performance.
public function
?– Renan Gomes
Even if you’re just studying OOP, it would be nice for you to use a problem that you can formulate well. When you try to discuss the solution to a modeling problem that is not well defined it is easy to fall into endless ramblings that go around in circles and end up not adding much value. Note that it is not about knowing in depth all your needs to only then model, it is about knowing in depth a part of the problem, and from there model the solution for this part. If you want to update the question with more details about the problem I think we can help better.
– Caffé