Portal to view many-to-many Relation

[Marcwa19197]

Hi,

i am stuck for a few days creating an many-to-many relation and view/edit it via a portal, which shows the many-to-many.

for a better understanding i attached a screenshot of the relationship graph.

in the "Personen" table there will be basic informations of all persons (Name, LastName, Birthday, etc.)
a person could be an Employeee (Mitarbeiter) with additional informations, stored in the "Mitarbeiter" table. (RoomNumber, OfficeNumber, etc.)
a person can also be be a child. if an employee have a child (or more), the basic data for the child is saved in the "Personen" table and with the many-to-many relation the "Mitarbeiter.ID" will be mapped to the "Personen.ID" in the "Kinder" table.

so now i would like to have an layout, where i can add an Employee. first it is necassary to enter the basic informations of the employee (Name, Birthday, ..) to the "Personen" table. after that the "Mitarbeiter" table should be filled with work-related data (OfficeNumber, ...). after the employee basic data is entered and he has an "Mitarbeiter.ID" i will need to use a Portal to add childs, which are related to "Personen" and related to "Mitarbeiter".

so does anyboday has an idea how to do this? or can explain whats wrong with my database "design"?

 

Edited December 8, 2015 by Marcwa19197

[comment]

2 minutes ago, Marcwa19197 said:

a person can also be be a child

Did you perhaps mean that a person can have a child?

If I read this correctly, you have a one-to-one relationship between Persons and Employees (not sure why you need this, but let's leave it for now) and a one-to-many relationship between Persons and Children. Your relationships graph could look like this:

Note that there is no direct relationship between Employees and Children (and no EmployeeID in the Children table) - they are already related through their common parent table Persons. You can place a portal to Children on a layout of Employees and it will show all the children of the currently viewed employee.

If for some reason you wanted to create Children from a layout of Employees (again, I don't see why you would want to), you could use another occurrence of the Children table and relate it to Employees using PersonID as the matchfield (not EmployeeID as shown in your graph).

But I don't see why you could not do all your data entry from a layout of Persons: use a portal to Children to create the children, and related fields from the Employees table placed directly on the layout to both create and fill out the necessary details for an employee.

 

 

 

 

[Marcwa19197]

Hi,

first of all, thanks for the fast reply.

the problem we have is, that we would like to store all "basic data" (including Names, Birthdays, etc.) for every person in one table.

So, of course your concept will work, but than we will have names stored in the Children table and in the Persons table (and later in many more tables, for example in the Sposes Table).

a Person can be a Child, a Employee or a Spose. a employee can have many childs and one spose.

we need to know which childs, or which spose is associated to which employee

Edited December 8, 2015 by Marcwa19197

[comment]

If a Child is also a Person, then you should either not have a Children table at all, or use it only as a join table between Persons and another occurrence of itself - depending on whether a child can have more than one parent.

[Marcwa19197]

can you please share a screenshot of the relationships, just for understanding you correctly, thanks in advice!

[comment]

30 minutes ago, Marcwa19197 said:

can you please share a screenshot of the relationships

Answer my question first:

34 minutes ago, comment said:

depending on whether a child can have more than one parent

 

[Marcwa19197]

yes, in case if both parents are employees and they have the same childs. so the childs must be associated to both employees.

[comment]

Then you would be looking at something like this:

Note that in this arrangement, you cannot use a portal to create new children. You must create a child first as a Person, before you can associate it with another person as their child.

 

Edited December 8, 2015 by comment

[Marcwa19197]

Okay, works perfect!

Thanks for your help!