Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004

    Unanswered: Request help for optimized table design

    Hi all,

    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.

    Thanking in advance


  2. #2
    Join Date
    Jun 2004
    Provided Answers: 1
    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.

  3. #3
    Join Date
    Sep 2002
    Provided Answers: 1
    Hopefully your tables have sensible names like Customers and Sellers rather than A and B?!

    My preference would be for a 3rd table to show which sellers are active for each customer:

    create table customer_active_sellers
    ( cust_id references customers
    , seller_id references sellers
    , primary key (cust_id, seller_id)

    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
    from customer_active_sellers;

  4. #4
    Join Date
    Nov 2004
    Hi andrewst

    Thank you very much.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts