Search code examples
data-management

Best way to store company and employee data

I want to create an application where shop owner can register their shop and can store their customer details. My question is what will be the best way to store shop and their corresponding customer information.
I can create store and customer table and can have foreign key mapping. But is there any alternative and more secure way of doing this? Here security is primary concern. One shop owner should not be able to see other shop owner details.

Solution

  • You can create a third table with the name of shop_customers where you have both the ids of shop and customer as foreign key mapping.

    Create table shops( shop_id integer, primary key (shop_id) );

    Create table customers( customer_id integer, primary key (customer_id) );

    CREATE TABLE shop_customers( shop_id integer, customer_id integer, Primary Key (shop_id,customer_id), Foreign Key (shop_id) REFERENCES shops(shop_id), Foreign Key (customer_id) REFERENCES customers(customer_id) );