postgresql – Efficiently find the ultimate parent in multilevel one-to-many tables

Suppose I have a database of multilevel one-to-many tables (a fictitious example):

CREATE TABLE users (
  id SERIAL PRIMARY KEY
);

CREATE TABLE properties (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id)
);

CREATE TABLE rooms (
  id SERIAL PRIMARY KEY,
  property_id INTEGER NOT NULL REFERENCES properties(id)
)

CREATE TABLE rates (
  id SERIAL PRIMARY KEY,
  price INTEGER NOT NULL,
  room_id INTEGER NOT NULL REFERENCES rooms(id)
)

For example, suppose a user wants to update that price in the rates But I want to make sure that he is allowed to – d. H rate belongs to a room that belongs to one property that the user owns.

What would be the best / most efficient way to do this? Is there a way to add a deduced user_id Column to rates Table that is automatically filled when the rows are added to the table rates Table so I can use it later in a query like the following:

UPDATE rates SET price=100 WHERE (id=30) AND (user_id=1)`

Java – Lazy Initialization Exception with 2 entities with the same OnetoMany Lazy Load column in session

I get one Lazy Initialization Exception for a onetomany associated column in idle state. It is not the usual session not found problem or eager and lazy on . Please go through the scenario below

I have a Basic Entity class with me discriminator column specified therein. There are two child entity classes for different discriminator values.

base class

@DiscriminatorColumn("cust_type") 
@Table(name = "CUSTOMER_PROFILE") 
Customer
{

@OneToMany(fetch = FetchType.LAZY, cascade = { CascadeType.ALL }) @JoinColumn(name = "common_id", referencedColumnName = "common_id") 
private Set CommonIdentifier = new HashSet();

}

Child class 1 with discriminator value TYPE 1

@Entity(name="CustomerType1") 
@DiscriminatorValue("TYPE1") 
CustomerType1 extends Customer
{
@ManyToOne(optional = true) @JoinColumn(name = "type2_id") private CustomerType2Entity customerType2; // fetch eager
}

Child class 1 with discriminator value TYPE 2

@Entity(name="CustomerType2") 
@DiscriminatorValue("TYPE2") 
CustomerType2 extends Customer
{


}

Here the CUSTOMER with discriminator Type 1 has a join column type2_id which is actually referenced to the same table but to one Customer with different discriminator value.

Even in the base class, there is one onetomany pillar common_id ,

Now there is a scenario in which the customer Type 1 pictured on Type 2 both have the same common_id which is lazy set. Then I'll get Type1 Customer automatically Type2 will also pick.

Everything works fine if there are different common_id for these 2 customers. But if both have the same common_id, then if I load Type1.CommonIdentifier lazy, I get a lazy initialization exception because there are 2 entities with the same collection with the same ID there hibernate Session,

How can I solve this problem? A redesign is not possible table or do CommonIdentifier Eager as a legacy application.

Ask for advice.

sql – One-to-many relationship design

In my design of the database, I notice the following case:

I have men in a table that can be assigned Employee O vehiclesthat are in their respective tables.

The relationship I have to make is one too many. My doubt in the design is whether I should create a table, for example equipment_assigned with the fields: TEAM_ID.employee_id.id_vehicle and if I assign a team to an employee, leave the id_vehicle field null, and if I assign a team to a vehicle, leave the id_employed field null.

I would like to know if this is accepted in the design or if there is a better solution for this type of case

postgresql – Defining and querying tables with mutually exclusive one-to-many relationships

Hello, I am the following situation.

I have 5 tables

entity
------------
identity
Entity
...
...
idCountry_fkey
idRegion_fkey
idAggregate_fkey

country
---------------
idCountry
country name

region
---------------
idCountry_fkey
idRegion
region name

aggregate
---------------
idAggregate
Aggregate name

Aggregate_to_Country
----------------------
IDAC
idCountry_fkey
idAggregate_fkey

I've enforced mutual exclusivity with the following restriction on the entity table

                CHECK (
(("idAggregate" IS NULL) AND ("idRegion" IS NULL) AND ("idCountry" IS NOT NULL))
OR
(("idAggregate" IS NULL) AND ("idRegion" IS NOT NULL) AND ("idCountry" IS NULL))
OR
(("idAggregate" IS NOT NULL) AND ("idRegion" IS NULL) AND ("idCountry" IS NULL))
);

I have following questions:

  1. Is this the right way to deal with this situation?
  2. How do I query to have:
index | Entity Name | Aggregate | Country | Region | value
-------------------------------------------------- --------
case1 | A | Eurozone | null | null | 1
-------------------------------------------------- --------
case2 | B | LATAM | Brazil | null | 22
-------------------------------------------------- --------
case3 | C | Europe | Italy | Lazio | 44
  1. Case 1) when the unit is connected to the unit
  2. Case 2) If entity is
    by country, and the country is in an aggregated case
  3. Case 3) If the entity is connected to the region and the region is connected to a country and the country is in an aggregate

How can I create a one-to-many relationship on the same model in Ruby on Rails?

I have a "categories" model that I need to use to create categories within a category. This would be a one-to-many relationship to the same model. I've made these relationships, but on other models, I do not understand how I can create that relationship, create it, or what information could be used to achieve it?

Entity component system – How are one-to-many relationships usually expressed in the ECS architecture?

I'm trying to implement a simple ECS to understand the concepts, and I'm having some trouble thinking about how to use it in cases where there is no one-to-one mapping between entities and components ,

For example, an example of ECS, as I understand it, is that you have defined a "sketchable" component that contains a mesh ID and a material ID that are required for drawing. This is easy to understand if you want a game like Super Mario, where each character is represented by exactly one sprite at all times. But what if you need more than one mesh to draw some characters?

Is it common to allow more than one component of the same type for an entity? Or would you normally have an entity for each component you want to draw and implement a kind of "join table" to map subcomponents to a parent?

Powerapps – adding values ​​in a one-to-many connection

I have two tables, sales and articles. The sales table has an ID (and some other values ​​that are currently not relevant), and the item table has its own ID, the ID of the sale, and a value.

I want to see the total value of the sale by adding the individual values ​​of the individual items that match the ID of the sale. However, I have no idea how this should happen.

Thanks in advance for any help you can give me