Good question! I left this out the original question: I want to display the products that are purchasable for that vendor. Hence, ahead of time I'd like to know what his possibilities are. Your answer below is exactly how I'd do it if I was always listing the products for sale to each vendor and then storing their purchased items (which of course will be done, but the question I'm raising here comes before that---when we're showing them the list of things they can buy).
Leland F. Jackson, CPA wrote: > Assuming you have a transaction table of purchases of products from > vendors, and a transaction table of sale of products to customers (eg > stores), why not include in the purchase transaction table a field to > hold the vendor's primary key and the products primary key. Likewise in > the transaction table of sales to customers (eg stores) you could have a > field to hold the primary key of each customer and a field to hold the > primary key of each product. > > Then you could create foriegn key restrains in the transaction tables > back to corresponding parent tables to maintain referential integrety. > In most databases like PostgreSQL, an index would automatically be > created on each foriegn key restraint. Then you could join the parent > tables back to their respective transaction tables to access the > information you need, while maintain referential integrity between the > tables. This would also better normalize data between the tables within > the database. > > Regards, > > LelandJ > > MB Software Solutions wrote: > >> I've got the following tables: Customers and Products. I want to >> create an intersection table called Customer_Products to house the >> specific products that each customer gets. In this example, the >> customers are grocery stores, and the products are bakery items. Not >> all Customers (stores) will sell the same products, but most will share >> the majority of products for their selling. >> >> Do I take the minimal data approach and create an exception table, >> listing the products that the specific Customer does NOT carry (option >> 1), or do I store each valid product the customer carries (option 2)? >> >> So for option 1, to get the products for a given customer would be this >> SQL (EXCLUSION approach): >> select * from products where ipk not in (select iproductid from >> customers_products where icustid = ?viCust) >> >> Or the sql for option 2 (INCLUSION approach): >> select * from products where ipk in (select iproductid from >> customers_products where icustid = ?viCust) >> >> I figured that if most of the time, the majority of products are >> available to a customer, it'd be better to choose option 1, because it'd >> be less intensive data operations. >> >> Your comments appreciated...tia! >> --Michael >> >> > > > [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
