public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tony Shelver <[email protected]>
To: pgsql-general <[email protected]>
Cc: Rich Shepard <[email protected]>
Cc: Muhammad Usman Khan <[email protected]>
Subject: Re: DDL issue
Date: Fri, 13 Sep 2024 07:20:24 +0200
Message-ID: <CAG0dhZDFYNzCSvAgs3rTmnarXJrR38SLMRb96kq_0k+zam9ZjA@mail.gmail.com> (raw)
In-Reply-To: <CAPnRvGt_=bWgTKaZSaK=K_TXJYHQ3oOJckvzUByfotCuGoMNzw@mail.gmail.com>
References: <[email protected]>
<CAPnRvGt_=bWgTKaZSaK=K_TXJYHQ3oOJckvzUByfotCuGoMNzw@mail.gmail.com>
On Fri, 13 Sept 2024 at 06:43, Muhammad Usman Khan <[email protected]>
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',
> '[email protected]');
> -- 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 = '[email protected]';
>
> Output:
>
> first_name | last_name | email | dairy_name | location | phone_number
> ----------- |----------- |--------------------- |------------|------------|--------------
> usman | khan | [email protected] | Dairy A | Location A | 123456789
> usman | khan | [email protected] | Dairy B | Location B | 987654321
> usman | khan | [email protected] | Dairy C | Location C | 111222333
> usman | khan | [email protected] | Dairy D | Location D | 444555666
> usman | khan | [email protected] | Dairy E | Location E | 777888999
>
>
> On Fri, 13 Sept 2024 at 04:01, Rich Shepard <[email protected]>
> 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.
view thread (2+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: DDL issue
In-Reply-To: <CAG0dhZDFYNzCSvAgs3rTmnarXJrR38SLMRb96kq_0k+zam9ZjA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox