I want to know best way to load all columns from child table to parent table in one-to-many

I want to know best practice to add primary key to all columns in a table. reason why I ask is, I have a one-to-many relationship and only way I can send all columns to parent table is by adding key value.

class address(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    # user_add_id = db.Column(db.Integer, unique=False)
    street_address = db.Column(db.String(120), primary_key=True, unique=False)
    city = db.Column(db.String(120), primary_key=True, unique=False)
    state = db.Column(db.String(120), primary_key=True, unique=False)
    zip_code = db.Column(db.String(120), primary_key=True, unique=False)
    latitude = db.Column(db.String(80), primary_key=True, unique=False)
    longitude = db.Column(db.String(80), primary_key=True, unique=False)
    user_add_id = db.Column(db.Integer, db.ForeignKey('doctors.id'), nullable=False)
    user_add_rel = db.relationship('doctors', backref='address', lazy='joined')

>Solution :

In a one-to-many relationship between a parent table and a child table, where multiple records from the child table are associated with a single record in the parent table, you can load all columns from the child table into the parent table using a process called "aggregation" or "concatenation." Here’s a general approach to achieve this:

1. Identify the common key between the parent and child tables. This is the column that establishes the relationship between the two tables.

2. Perform an aggregate function (e.g., GROUP_CONCAT in MySQL, STRING_AGG in SQL Server, LISTAGG in Oracle) on the desired columns in the child table, grouping them by the common key. The aggregate function combines the values from multiple rows into a single, comma-separated value.

3. Use an UPDATE statement to update the parent table, setting the desired column in the parent table equal to the result of the aggregate function from step 2.

Here’s an example using SQL syntax:

-- Step 1: Identify the common key
ALTER TABLE child_table ADD parent_id INT;

-- Step 2: Perform aggregate function on child table
SELECT parent_id, GROUP_CONCAT(child_column SEPARATOR ', ') AS concatenated_values
FROM child_table
GROUP BY parent_id;

-- Step 3: Update parent table with concatenated values
UPDATE parent_table
SET parent_column = (
    SELECT GROUP_CONCAT(child_column SEPARATOR ', ')
    FROM child_table
    WHERE child_table.parent_id = parent_table.id
)
WHERE EXISTS (
    SELECT 1
    FROM child_table
    WHERE child_table.parent_id = parent_table.id
);

Note that this example assumes that the parent table has an id column, and the child table has a foreign key parent_id that references the parent table’s id. Adjust the column names and syntax according to your specific database system.

Keep in mind that aggregating or concatenating multiple values into a single column in the parent table might not always be the best approach. Consider the potential impact on data integrity, query performance, and the need for data normalization in your specific use case.