Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1soy9j-00FElk-Ij for pgsql-general@arkaria.postgresql.org; Fri, 13 Sep 2024 04:43:40 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1soy9h-00ARTH-T7 for pgsql-general@arkaria.postgresql.org; Fri, 13 Sep 2024 04:43:37 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1soy9h-00ARSd-9Y for pgsql-general@lists.postgresql.org; Fri, 13 Sep 2024 04:43:37 +0000 Received: from mail-pj1-x102c.google.com ([2607:f8b0:4864:20::102c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1soy9Z-000uPo-JG for pgsql-general@postgresql.org; Fri, 13 Sep 2024 04:43:35 +0000 Received: by mail-pj1-x102c.google.com with SMTP id 98e67ed59e1d1-2d8f06c2459so433285a91.0 for ; Thu, 12 Sep 2024 21:43:29 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=bitnine-net.20230601.gappssmtp.com; s=20230601; t=1726202608; x=1726807408; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=zz4cFDtM/k72gCxD8z7wm26E07CkQ232yM10Q3nYgF0=; b=WpYhc+7Re67e7RzomzsSlxcwYzdpoqelic91f5RULC+ssMIdKbQKp4PGpQ1xzHp0cB TmkhzqNjWw6PeLe3mVRgP7Y4Wb61Me4lTTEeLrLZ6LdfhJa1/agTFlWDKcyBa3YBbu/8 9fvg7p81v8CKK8vqeoKRxu55A5YAEdlekfwrL/eyj5zf71cnXr+bxSUw3xXatGjlfQr5 N4/jvqfaywd9Sp1ii9Apgm7/wK3OiW+qR6JOfqG1rsazwaP2Fv0ec6aw1wX94f1LxZ4Y Z+mYvTBDnN+va43rPG21Bq42qGuStCHKIHTWYM+28jXm3/PxKSvFEp4kAqMj5PvcWeYg upMA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1726202608; x=1726807408; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=zz4cFDtM/k72gCxD8z7wm26E07CkQ232yM10Q3nYgF0=; b=iUouiTZ9dVlOCxyTjs2Rfa8lpvfSaG4FGNFUgNxTJqZLX4rt1uxYji2nYwgqiT4AMr 3b9esrnr0w7XJI9drnt8nXAVekz8A8bZJZbP9Gj5Yq5c8vWDH84YroMU8tm/Jupw5BJ8 YEoAtHxZTCTheH284cRKVhDchT/Yg2uMM5ys2n7cAC/MbqSumZnEJ/7aQKHSBHwcPBOu ZoNbjo4Y6tfxdQrRg0yOuMyDbeN0HU48VQzDpnqWoig4r7OanXGxhEmfx3yij/aWbuP2 Kqh0JliJu+f5d6xxn+3yY8pc/Ztx1UNB95iIiZqp32N/gBY5WOZtcT0aLE0WXz89EgXD nHFg== X-Gm-Message-State: AOJu0Yz2zwFLjdQXY2pHyZ0uL7Nc5OCVlS1VDXe/1CSoJgeH4So1BEnj cLM0/1gic9atimw+mJzN7ctlHB3jchFQ+bhZ8WOubAwRYi2Bc9UON5vpvlI1pagNzjIJfvjIVM+ 2v2junee2Peclqm5bzpRANiF/2mknC3x9ir99wQ== X-Google-Smtp-Source: AGHT+IGePbn1NBxD+RmbStRAsXvE6VcFLhKCzIwWLYW/R0JsBIgik5m0mdlO8YVS25uu6yHBhi3yIRy3nTTHpxjpiOw= X-Received: by 2002:a17:90b:3512:b0:2d3:c0d4:2c33 with SMTP id 98e67ed59e1d1-2dbb9e1cf39mr1978779a91.17.1726202607564; Thu, 12 Sep 2024 21:43:27 -0700 (PDT) MIME-Version: 1.0 References: <91bda8-3bdb-d93-6c17-41a03a3de27a@appl-ecosys.com> In-Reply-To: <91bda8-3bdb-d93-6c17-41a03a3de27a@appl-ecosys.com> From: Muhammad Usman Khan Date: Fri, 13 Sep 2024 09:43:14 +0500 Message-ID: Subject: Re: DDL issue To: Rich Shepard Cc: pgsql-general@postgresql.org Content-Type: multipart/alternative; boundary="0000000000003c14a50621f8ddc6" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000003c14a50621f8ddc6 Content-Type: text/plain; charset="UTF-8" Hi, To handle this situation in PostgreSQL, you can model the data in a way that maintains a single entry for each owner in the people table while linking the owner to multiple dairies through a separate dairies table. This is a typical one-to-many relationship (one person can own many dairies). The following is testing scenario which might help you -- Create people table (one entry per person) CREATE TABLE people ( person_id SERIAL PRIMARY KEY, first_name VARCHAR(100), last_name VARCHAR(100), email VARCHAR(100) UNIQUE ); -- Create dairies table (each dairy will be linked to a person) CREATE TABLE dairies ( dairy_id SERIAL PRIMARY KEY, dairy_name VARCHAR(100), location VARCHAR(100), phone_number VARCHAR(15), person_id INT REFERENCES people(person_id) ON DELETE CASCADE ); -- Insert a person (owner) into people table INSERT INTO people (first_name, last_name, email) VALUES ('usman', 'khan', ' usmankhan@example.com'); -- Insert multiple dairies owned by the same person INSERT INTO dairies (dairy_name, location, phone_number, person_id) VALUES ('Dairy A', 'Location A', '123456789', 1), ('Dairy B', 'Location B', '987654321', 1), ('Dairy C', 'Location C', '111222333', 1), ('Dairy D', 'Location D', '444555666', 1), ('Dairy E', 'Location E', '777888999', 1); SELECT p.first_name, p.last_name, p.email, d.dairy_name, d.location, d.phone_number FROM people p JOIN dairies d ON p.person_id = d.person_id WHERE p.email = 'usmankhan@example.com'; Output: first_name | last_name | email | dairy_name | location | phone_number ----------- |----------- |--------------------- |------------|------------|-------------- usman | khan | usmankhan@example.com | Dairy A | Location A | 123456789 usman | khan | usmankhan@example.com | Dairy B | Location B | 987654321 usman | khan | usmankhan@example.com | Dairy C | Location C | 111222333 usman | khan | usmankhan@example.com | Dairy D | Location D | 444555666 usman | khan | usmankhan@example.com | Dairy E | Location E | 777888999 On Fri, 13 Sept 2024 at 04:01, Rich Shepard wrote: > I have one name in the people table who owns 5 different dairies with three > different phone numbers, but all 5 have the the same email address. > > The five dairies each has its own name and location while the people table > has five rows with the same last and first names and email address. > > Is there a way to have only one entry for the owner in the people table > while related to five different company names? In some industries, such as > dairy farms, this is not an unusual situation. > > TIA, > > Rich > > > > --0000000000003c14a50621f8ddc6 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi,
To handle this situat= ion in PostgreSQL, you can model the data in a way that maintains a single = entry for each owner in the people table while linking the owner to multipl= e dairies through a separate dairies table. This is a typical one-to-many r= elationship (one person can own many dairies).
The following = is testing scenario=C2=A0which might help you

-- Create people table (one entry per person)=C2=A0
CREATE TABLE people ( person_id SERIAL PRIMARY KEY,= =C2=A0
first_name VARCHAR(= 100),=C2=A0
last_name VARCHAR(100),=C2=A0
email VARC= HAR(100) UNIQUE );=C2=A0

-- Cre= ate dairies table (each dairy will be linked to a person)=C2=A0
CREATE TABLE dairies ( dairy_id SERIAL PRIMARY KEY,= =C2=A0
dairy_name VARCHAR(= 100),=C2=A0
location VARCHAR(100),=C2=A0
phone_number VARCHAR(15),=C2=A0
<= div>person_id INT REFERENCES people(person_id) ON DELETE CAS= CADE );=C2=A0

-- Ins= ert a person (owner) into people table=C2=A0
INSERT IN= TO people (first_name, last_name, email) VALUES ('usman', 'kha= n', 'usmankhan@example.com');=C2=A0
-- Insert multiple dairies owned by the= same person=C2=A0
INSE= RT INTO dairies (dairy_nam= e, location, phone_number, person_id) VALUES=20 ('Dairy A', 'Location A', '123456789', 1), ('Dairy B', 'Location B', '987654321', 1), ('Dairy C', 'Location C', '111222333', 1), ('Dairy D', 'Location D', '444555666', 1), ('Dairy E', 'Location E', '777888999', 1);=C2=A0=C2=A0

SELECT p.first_name, p.last_= name, p.email, d.dairy_name, d.location, d.phone_number
FROM people pJOIN dairies d ON p.person_id =3D d.person_id
WHERE p.email =3D 'usmankhan@example.com';
<= /div>

Output:

first_name  | last_name   | email =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0   | dairy_name | location =C2=A0 | phone_number
----------- |=
-----------  |---------------------  |------------|------------|-----------=
---
usman =C2=A0 =C2=A0 =C2=A0 | khan =C2=A0 =C2=A0 =C2=A0  | usmankhan@example.com | Dairy A =C2=A0 =
=C2=A0| Location A | 123456789
usman =C2=A0 =C2=A0 =C2=A0 | khan =C2=A0 =
=C2=A0 =C2=A0  | usmankhan@example=
.com | Dairy B =C2=A0 =C2=A0| Location B | 987654321
usman =C2=A0 =
=C2=A0 =C2=A0 | khan =C2=A0 =C2=A0 =C2=A0  | usmankhan@example.com | Dairy C =C2=A0 =C2=A0| Location C | =
111222333
usman =C2=A0 =C2=A0 =C2=A0 | khan =C2=A0 =C2=A0 =C2=A0  | usmankhan@example.com | Dairy D =
=C2=A0 =C2=A0| Location D | 444555666
usman =C2=A0 =C2=A0 =C2=A0 | khan =
=C2=A0 =C2=A0 =C2=A0  | usmankhan@=
example.com | Dairy E =C2=A0 =C2=A0| Location E | 777888999

On Fri, 13 Sept 2024 at 04:01, Rich Shepard <rshepard@appl-ecosys.com> wrote:
I have one name in= the people table who owns 5 different dairies with three
different phone numbers, but all 5 have the the same email address.

The five dairies each has its own name and location while the people table<= br> has five rows with the same last and first names and email address.

Is there a way to have only one entry for the owner in the people table
while related to five different company names? In some industries, such as<= br> dairy farms, this is not an unusual situation.

TIA,

Rich



--0000000000003c14a50621f8ddc6--