Database Designers and Developers - Welcome!
I am designing a database schema for my project. The project involves the relationship between 'Producers' and 'Consumers', but if both 'User' and 'Organization' can act as 'Producer', then only 'User' can act as 'Consumer' so far (although this just for now). If we consider the object model of my project, then it is based on two basic interfaces - 'Manufacturer' and 'Consumer'. Relationships will occur between instances of the main classes 'Organization' and 'User', with the class 'Organization' implementing the 'Manufacturer' interface, and the class 'User' implementing the interfaces 'Manufacturer' and 'Consumer'. For example, for a certain period of using my application, some users must be able to sell goods to other users, and be able to buy goods from someone.
In the future, I plan to integrate the most demanded payment systems into the project. Who has experience with payment systems, tell me what changes it will make to the model and data scheme. Do I need to add entities 'Legal entity' and 'Physical person'?
I designed the ER-diagram according to the above description, but it has several disadvantages.
First, there is no complete correspondence between the ER description and the UML description. Do I need to allocate separate tables for interfaces? I do not know this. Indeed, in this case, according to the ER description, we can say that 'Producer' and 'Consumer' are superclasses for 'Organization' and 'User', but in fact they are just interfaces.
Secondly, I need as simple a design as possible so that access to the repository is quite flexible, without unnecessary 'JOINs'. Maybe you should completely abandon the implementation of interfaces? But I am for the use of all the wealth of the PLO. Is it possible to use interface implementations at the database level? I know that you can use inheritance. But I do not know how these tricks will affect performance - since all previous projects did not require the use of superclasses and interfaces. I will use PostgreSQL as a relational database.
If I enter roles for the 'Producer' and 'Consumer' tables, then I will have to endow them with attributes specific to the 'User' and 'Organization' entities. On the one hand, if the number of roles increases, then the number of attributes will increase. This path entails overloading of table attributes. And for different roles, some attributes will correspond to NULL values. On the other hand, one attribute for one role may correspond to several attributes for another role. For example, the attribute 'name' for the entity 'Organization' corresponds to the attributes 'firstname', 'middlename', 'lastname' and 'username' for the entity 'User'. Thirdly, I still want to separate the models of 'Producer', 'Organization', 'User' and 'Consumer' from each other.
Please share your experiences and implementations of successful data schemes.
