How to create a schema and how to build relationship between tables in liferay dxp?

Question

I have done many-many relation between Employee and Department table by using mapped-table attribute ,It was generated third table by name Employee_department, In Employee_department table why liferay generate one extra column by name CompanyId and it leads error in persistenceImpl class.

<service-builder package-path="com_m2" auto-namespace-tables="false">
    <namespace>emp</namespace>


   <entity name="Employee" local-service="true" remote-service="false" table="Employee" uuid="true">
        <column name="eid" type="long" primary="true"></column>
        <column name="name" type="String"></column>
        <column name="address" type="String"></column>
        <column name="deptid" type="Collection" entity="Department" mapping-table="Employee_department"/>
   </entity>
   
    <entity name="Department" local-service="true" remote-service="false" table="Department" uuid="true">
        <column name="deptid" type="long" primary="true"></column>
        <column name="department" type="String"></column>
        <column name="eid" type="Collection" entity="Employee" mapping-table="Employee_department"/>
    </entity>
    
</service-builder>

-------------------------------------------------------------------------------

create table Employee_department (
    CompanyId LONG not null,
    deptid LONG not null,
    eid LONG not null,
    primary key (deptid, eid)
);

Answer 1

A "company" in the Liferay API can be found as "Virtual Instance" on the UI: You can have multiple completely separate collections of data, for multi-tenancy.

I'm not sure why the field is generated when your two entities don't have such a field - that might be a bug. But it points to what you could do to blend your entities into Liferay's stock entities. This will make it easier to also use them from Liferay's Asset- and Info-Frameworks.

A whole new (and different) solution would be to leverage Liferay Objects and go with client-side code - but that's just here for completeness and not what you're asking for.

Answer 2

You defined two entities in service.xml but the service builder will create 3 tables and the third one is mapping tables which map the Employee and Department primary keys.

That table will hold the records of your employee and department respectively. So when you getEmployeeDepartment(employeeId) it will return the list of Departments and vice versa.

UPDATE I was able to find the same error in this topic via the Liferay Dev community here https://liferay.dev/ask/questions/development/re-service-builder-persistance-impl-errors-in-case-of-tablemapper-5

The issue should be fixed after we add the column <column name="companyId" type="long" /> into both two entities.