Unanswered: Request help for optimized table design
I need help regarding table design.
There are two tables: Table A -- Contains Customers Details(cust_id, Cust_Name)
Table B -- Contains Sellers Details(Seller_id, Seller_Name)
Let us assume Table A contains 10,000 rows and Table B contains 10,000 rows.
A customer from Table A can select 'N' of Sellers from Table B, Then only selected Sellers are shown as active remaining shown as deactive for each Customer. That is there is many to many relationship between Customers and Sellers. These details has to store separately in a Table. How to stores these all details in a table, including Active and Non Active Sellers corresponding to each Customer in an Optimised Way.
Many-to-Many relationships are a "No No" when it comes to database design. What you need to get over this problem is a link table that has a One-to-Many relationship from each of the two other tables (i.e. a link can belong to one and only one customer, a customer record can belong to one to many links, with the same being true for sellers). You will then end up with link records that can each be 'tied' to a seller & a customer (probably by seller's ID & customer's ID, but that depends on how the customer & seller tables are set up.
90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.
The nonactive sellers for each customer can then be derived via a view:
create view customer_nonactive_sellers as
select c.cust_id, s.seller_id
from customers c, sellers s -- No WHERE clause: deliberate Cartesian product!
select cas.cust_id, cas.seller_id