CODEWITHSUNDEEP
oracle-databasemany-to-manyrelationshiporacle-apex
ulu
ulu

Reputation: 1

Oracle APEX: How to connect two tables with a n:m relationship

I have two tables: Server and Host. They shall have a m:n relationship. How can I connect them? Do I have to set up a third table Server_Host with the PrimaryKeys of these two tables? Or does APEX do this automatically somehow?

Thank you for helping!

Upvotes: 0

Views: 1203

Answers (2)

Joe
Joe

Reputation: 3347

Quick answer is "Yes" and "No".

Longer answer: If it is truly M:M then it is required in a relational database that respects the normal forms and the two tables must have the PKs from the other two tables as FKs, although they do not have to be - and arguably/depending on how used in queries, should not be - part of the PK for the intersection table.

Consider using SQL Developer (free) to model the three tables and create the DDL.

And, are you sure there is only one intersection table? Good DB design dictates that you consider the relationship attributes. For example, Customer to Catalog Item is M:M - but adding an Order table does not completely resolve the M:M, as a M:M exists between Order and Catalog Item, so another intersection table, Line Item, is needed between them.

Please think about this and also specify the nature of the relationship between server and host. For example. I'm not sure why server and host are M:M in your case without you specifying the nature of the relationship being modeled and attributes of the tables and relationship. Is a server assigned to a host? So a host can be the host for 1:M servers? How is it M:M? Or is this a relationship over time or a given point in time? That affects the model as well.

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143013

As this is m:n relationship:

Do I have to set up a third table Server_Host with the PrimaryKeys of these two tables?

In my opinion, yes - that would be a good idea, and is easy to maintain.

Or does APEX this automatically somehow?

No, Apex won't do that automatically. It can not, it is part of data model. Apex is just a tool that accesses your data, it doesn't establish any relations among it.

Upvotes: 0

Related Questions