Re: DDL issue

On Fri, 13 Sept 2024 at 06:43, Muhammad Usman Khan <usman(dot)k(at)bitnine(dot)net>
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(at)example(dot)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(at)example(dot)com';
>
> Output:
>
> first_name | last_name | email | dairy_name | location | phone_number
> ----------- |----------- |--------------------- |------------|------------|--------------
> usman | khan | usmankhan(at)example(dot)com | Dairy A | Location A | 123456789
> usman | khan | usmankhan(at)example(dot)com | Dairy B | Location B | 987654321
> usman | khan | usmankhan(at)example(dot)com | Dairy C | Location C | 111222333
> usman | khan | usmankhan(at)example(dot)com | Dairy D | Location D | 444555666
> usman | khan | usmankhan(at)example(dot)com | Dairy E | Location E | 777888999
>
>
> On Fri, 13 Sept 2024 at 04:01, Rich Shepard <rshepard(at)appl-ecosys(dot)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
>> 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.