public inbox for [email protected]
help / color / mirror / Atom feedRe: DDL issue
2+ messages / 2 participants
[nested] [flat]
* Re: DDL issue
@ 2024-09-13 05:20 Tony Shelver <[email protected]>
2024-09-13 12:42 ` Re: DDL issue Rich Shepard <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Tony Shelver @ 2024-09-13 05:20 UTC (permalink / raw)
To: pgsql-general; +Cc: Rich Shepard <[email protected]>; Muhammad Usman Khan <[email protected]>
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: DDL issue
2024-09-13 05:20 Re: DDL issue Tony Shelver <[email protected]>
@ 2024-09-13 12:42 ` Rich Shepard <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Rich Shepard @ 2024-09-13 12:42 UTC (permalink / raw)
To: pgsql-general
On Fri, 13 Sep 2024, Tony Shelver wrote:
> 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);
Thanks, Tony.
Regards,
Rich
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-09-13 12:42 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-09-13 05:20 Re: DDL issue Tony Shelver <[email protected]>
2024-09-13 12:42 ` Rich Shepard <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox