In my practice, there are often problems mapping an OOP approach to relational databases in the case of fields of the same type in the same class. I can not understand how to solve this problem in the most effective way.

More detailed example:

I have a Person entity that has the address and workAddress . Moreover, both subentities have the same set of fields.

In code, it is something like this (pseudocode)

 class Person Address address Address workAddress

How to implement this using programming languages ​​is understandable. The question arises how to do this in the database. I can do, for example, like this (pseudo sql ):

 CREATE TABLE address (id SERIAL PRIMARY KEY); CREATE TABLE PERSON ( id SERIAL PRIMARY KEY, address FOREIGN KEY (address.id), workdAddress FOREIGN KEY (address.id) );

This will work, but this is not entirely correct from the point of view of the DB theory. After all, we will have one-to-many from the address-person. Even twice.

You can still go this way:

 CREATE TABLE PERSON ( id SERIAL PRIMARY KEY ); CREATE TABLE address_type ( id SERIAL PRIMARY KEY, name VARCHAR(50) ); CREATE TABLE address ( id SERIAL PRIMARY KEY, person_id FORIGN KEY(person.id), address_type FORIGN KEY (address_type.id) );

Here we get the right solution from the point of view of the relational model, but from a very bad solution for OOP. Of the minuses here: an additional JOIN , an additional table with types, it is inconvenient to work with this in the code (you need to go through all the addresses to find the right one or supplement the selets).

I was looking for a solution in the most popular ORM - Hibernate . I did not find there anything to solve such a problem (maybe I looked badly).

Are there any common practices, tools or mechanisms to solve such problems?

  • one
    This will work, but this is not entirely correct from the point of view of the DB theory. After all, we will have one-to-many from the address-person. Even twice. What's wrong with that? And why? Everything here is correct and logical - provided that the process being modeled assumes that the same address can be both a worker and a residence permit (it does not matter for one or different people). And if not, then the correctness of the structure depends on whether one entity is modeled "address" with the attribute "registration / work", or two different entities (in the second case there should be two separate address tables). - Akina
  • @Akina I completely agree. But there is incomprehensibility with the storage of unique addresses. The question is how do I recognize already existing addresses. To do, and then an update or? - faoxis
  • Checking for the existence of an address is done standardly - a request to search for this address. If records 0 - the address is new, otherwise not. And the returned ID is used .. alas, there is no reason for a unique index across the entire field - a big one, but the hash does not haantize. - Akina

2 answers 2

I understand that the question is to choose:

  1. Create several fields in the target table - links to some directory
  2. Create an additional table containing several records belonging to one record of the target table and a link to some directory (possibly with an indication of the type of connection and other parameters)

Both options are good, have the right to exist. Which one to choose depends on the requirements of the problem to be solved.

The first option can be chosen if the connection types of the two tables are rigidly fixed, do not change during the operation of the system, and their further expansion is not envisaged. Also, this solution is much simpler than the second.

The second option can be chosen if it is necessary to specify additional communication parameters (type, visibility, accessibility, multiplicity, priority) and if flexible control of the types of communication between these two tables is provided. For example, it is theoretically possible to store a history of changes in related data.

In connection with a specific address scheme, the first option is quite suitable for simplicity. If you suddenly need to keep a history of address changes, the second is more appropriate. Or suddenly, additional types of addresses may appear - for example, a delivery address, residence permit, accommodation, mail, etc.

    This will work, but this is not entirely correct from the point of view of the DB theory. After all, we will have one-to-many from the address-person. Even twice.

    Are you sure that you have one-to-many? especially with a work address? or you intentionally duplicate data for other reasons (security, etc.)

    if there is no reason for duplication, then you can model it as many-to-many using the association class

     create table Person ( id serial primary key, name text -- etc ); create table Address ( id serial primary key, street text -- etc ); create table PersonsAddresses ( person_id foreign key (person.id), address_id foreign key (address.id), type int --or text or enum, primary key(person_id, address_id, type) );
    • Are you sure that you have one-to-many? especially with a work address? I realized that I asked? the stump is clear, several different people may have the same address. And even one person in both fields. One address corresponds to several persons - what is your connection? - Akina
    • The question was addressed to the author’s statement "After all, we will have one-to-many by address-person. Even two times." and the fact that in the real world the connection of people with many-to-many addresses is also a “stump clear” :) but not the fact that this “clear stump” should always be reflected in the model. - Ramiz
    • Aah ... then, probably, it makes sense to explain it right in the answer - well, to remove ambiguities ... for example, with a quote from the question. - Akina