Subject: Re: DDL issue

From: Tony Shelver <[email-masked]>
Date: Fri, 13 Sep 2024 07:20:24 +0200

On Fri, 13 Sept 2024 at 06:43, Muhammad Usman Khan <[email-masked]>
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-masked]');
> -- 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-masked]';
>
> Output:
>
> first_name  | last_name   | email                 | dairy_name | location   | phone_number
> ----------- |-----------  |---------------------  |------------|------------|--------------
> usman       | khan        | [email-masked] | Dairy A    | Location A | 123456789
> usman       | khan        | [email-masked] | Dairy B    | Location B | 987654321
> usman       | khan        | [email-masked] | Dairy C    | Location C | 111222333
> usman       | khan        | [email-masked] | Dairy D    | Location D | 444555666
> usman       | khan        | [email-masked] | Dairy E    | Location E | 777888999
>
>
> On Fri, 13 Sept 2024 at 04:01, Rich Shepard <[email-masked]>
> 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.

---

From: Rich Shepard <[email-masked]>
Date: Fri, 13 Sep 2024 05:42:10 -0700 (PDT)

> dairy_id INT REFERENCES dairies(dairy_id);

Thanks, Tony.

Regards,

Rich