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 1soylm-00FJzg-9b for pgsql-general@arkaria.postgresql.org; Fri, 13 Sep 2024 05:22:59 +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 1soyll-00BUjG-Sy for pgsql-general@arkaria.postgresql.org; Fri, 13 Sep 2024 05:22:57 +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 1soyjY-00BOB7-Q5 for pgsql-general@lists.postgresql.org; Fri, 13 Sep 2024 05:20:41 +0000 Received: from mail-qv1-xf2d.google.com ([2607:f8b0:4864:20::f2d]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1soyjV-000ueA-1r for pgsql-general@postgresql.org; Fri, 13 Sep 2024 05:20:39 +0000 Received: by mail-qv1-xf2d.google.com with SMTP id 6a1803df08f44-6c56eec7fccso4033936d6.3 for ; Thu, 12 Sep 2024 22:20:37 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1726204836; x=1726809636; 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=vIdpBBl02sZBhrQic6mlQHMuyn50trHWLUuCBoqzVeg=; b=PoqoXCjZ6XOLVaJSnEvs8sZNcTSIpNeVo5h98X23E3rZpK14SxgiuFK0n62TGMLH+Y dsZS8PmwAHitHNKxrD4dMFA73N0RUkrMSCkyaS5Q/lqiOXBnHffDPgElEobuaUwfFt0x 3lCFhVC6f4lvMpS3qJeS7+5+O7RP9BB3VCIcWLh8vHd8YTOLw+rpXVN3zoO2R4jC6QMV vI8eolyl5BC8Cuhu4D9EUVvFZAjRwVaPZN89lTwBmdhKG44aFP1ow96D03N2OXNv1Vhe Q8nElLMxDi7U81HmpsTksM797jC4BMWOMpVTEVcz6P9Bq6xb0w61QN3Yrtv/5VomMfqz qmEQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1726204836; x=1726809636; 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=vIdpBBl02sZBhrQic6mlQHMuyn50trHWLUuCBoqzVeg=; b=kQhVbSB0Ty/0UiGkGRdyQyDZi5pbns6aShuKFb+wCfjQV9pgxXDl3bZcTLpyVY2I2E +6Ct7zfcTw2qz8lP8PfCc0QaU51V4GrQV8HWgs/4zvbTe2ce2+G2k9chfGzJTIAriZTJ CPALCLpXfUtIFsf1Xsf99PmSoBKeEYdXTmodr+igf9eYyKtT24RowPhWx6bZil6Sf7Nk lOeKvuNaiuyt6IAjdeba6V9+gT28rqbsEAxDjTCdRHFB7+zFaSW/5e9XHtcDiR/6mjm1 K7HUOovL1dr504hq/GM0gfH69vqC5AygHhx+AEgoGv2bop2BIzHeA7LVK8JV+7tNJHZ+ DUeA== X-Gm-Message-State: AOJu0YwdzFueBls2P0CFSZHQkOiDDxq3OilucHtTZLfb3VJoXFQr+qzK xalu7I9YRA2O3tCjsM0LgYK70+5QbbmZcIOQjd1RPyT9GUsOjoUiBkMICpWE3cMDolkKWVj/Fsp 7HYcappY/tOqIYb2hessDINyy1DGndy6h X-Google-Smtp-Source: AGHT+IG0bg4nl+yrnEvvLp8yZE3N68WPYGpR9btamog07PRmgyC0fxIM4uy1Ow34dsSnozOkfA7ce2nfZHotsBGWDhc= X-Received: by 2002:a05:6214:4a8e:b0:6c3:703b:2320 with SMTP id 6a1803df08f44-6c57df6e79emr34093466d6.5.1726204836179; Thu, 12 Sep 2024 22:20:36 -0700 (PDT) MIME-Version: 1.0 References: <91bda8-3bdb-d93-6c17-41a03a3de27a@appl-ecosys.com> In-Reply-To: From: Tony Shelver Date: Fri, 13 Sep 2024 07:20:24 +0200 Message-ID: Subject: Re: DDL issue To: pgsql-general Cc: Rich Shepard , Muhammad Usman Khan Content-Type: multipart/alternative; boundary="00000000000011fcb60621f96268" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000011fcb60621f96268 Content-Type: text/plain; charset="UTF-8" On Fri, 13 Sept 2024 at 06:43, Muhammad Usman Khan wrote: > 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 >> >> >> >> Or if you want to get even more flexible, where a dairy could have more than one owner as well as one owner having more than one dairy, you could create an intersection / relationship table. Something like -- Create people table (one entry per person) CREATE TABLE people_dairy_map ( pdm_id SERIAL PRIMARY KEY, first_name VARCHAR(100), last_name VARCHAR(100), email VARCHAR(100) UNIQUE ); phone_number VARCHAR(15), person_id INT REFERENCES people(person_id) dairy_id INT REFERENCES dairies(dairy_id); I would be careful of ON DELETE CASCADE, just on principle :) Obviously dairies table would no longer need the phone_number I would possibly suggest putting the email_address in the PDM table, just in case you find a use case where a dairy has different email addresses depending on the owner. For even more flexibility / future proofing, you could consider a 'type' column in the PDM table. For example, type = 'O' would be owner, type = 'M' would be manager, and so on. Now you have a full contacts representation that can be used for other purposes apart from denoting ownership details. The above 3 tables represents what we used to call a simple BOM structure (Bill of Materials), and is used for example in manufacturing, where a car has many parts, a part can be used in many parts. The same structure can also be used to represent lhe cases where a part is a sub-assembly of another part (assembly), and so on and so on, going many levels deep with the same basic 3 tables. --00000000000011fcb60621f96268 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

On Fri, 13 Sept 2024 at 06:43, Muhammad U= sman Khan <usman.k@bitnine.net> wrote:
Hi,
To handle this situation in PostgreSQL, you can model the d= ata in a way that maintains a single entry for each owner in the people tab= le while linking the owner to multiple dairies through a separate dairies t= able. This is a typical one-to-many relationship (one person can own many d= airies).
The following is testing scenario=C2=A0which might h= elp you

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

-- Create 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 VARCHA= R(100),=C2=A0
phone_number VARCHAR(15),=C2=A0
person_id INT REFER= ENCES people(person_id) ON DELETE CASCADE );=C2=A0

-- Insert a person (owner) into peo= ple table=C2=A0
-- Insert mul= tiple dairies owned by the same person=C2=A0
INSERT<= /span> INTO dairies (dairy_name, location, phone_number, perso= n_id) VALUES=20 ('Dairy A', 'Location A', &= #39;123456789', 1), ('Dairy B', 'Location B', &= #39;987654321', 1), ('Dairy C', 'Location C', &= #39;111222333', 1), ('Dairy D', 'Location D', &= #39;444555666', 1), ('Dairy E', 'Location E', &= #39;777888999', 1);=C2=A0=C2=A0

<= /div>
SELECT p.first_name, p.last_name, p.email, d.dairy_name, d.locati= on, d.phone_number
FROM people p
JOIN dairies d ON p.person_id =3D d.= person_id
WHERE p.email =3D 'usmankhan@example.com';

Output:

first_name  | last_=
name   | email =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0   | dairy_n=
ame | 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  | us=
mankhan@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 | kh=
an =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.c=
om | 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 ha= ve 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




Or if you want to get even more flexible, where a dairy could have more=20 than one owner as well as one owner having more than one dairy, you=20 could create an intersection / relationship table.

Something like
=C2=A0
-- Create people table (one entr= y per person)=C2=A0
CREATE TABLE= people_dairy_map ( pdm_id SERIAL PRIMARY KEY,=C2=A0
first_name VARCHAR(100),=C2=A0
last_na= me VARCHAR(100),=C2=A0
email VARC= HAR(100) UNIQUE );=C2=A0
phone_number VARCHAR(15),= =C2=A0
person_id INT REFERENCES people(= person_id)
dairy_id INT REFERENCES dairies(dairy_id);

I would be careful of ON DELETE CASCADE, just on principle= :)

Obviously dairies table would no longer need t= he phone_number
I would possibly suggest putting the email_addres= s in the PDM table, just in case you find a use case where a dairy has diff= erent email addresses depending on the owner.

For = even more flexibility / future proofing, you could consider a 'type'= ; column in the PDM table.=C2=A0 For example, type =3D 'O' would be= owner, type =3D 'M' would be manager, and so on. =C2=A0 Now you ha= ve a full contacts representation that can be used for other purposes apart= from denoting ownership details.

The above 3 = tables represents what we used to call a simple BOM structure (Bill of Mate= rials), and is used for example in manufacturing, where a car has many part= s, a part can be used in many parts.=C2=A0 The same structure can also be u= sed to represent lhe cases where a part is a sub-assembly of another part (= assembly), and so on and so on, going many levels deep with the same basic = 3 tables.
--00000000000011fcb60621f96268--