I’m working on a solution and I need some suggestions as to where to start. I have a customer base of about 150 accounts. And a product line of about 3,000 items. The customer accounts are broken down into four groups to identify a specific pricing group, A-D. Each group has its own selling price, across all products offered, identified in each customer record, i.e. account number, name, address, and pricing code.

What I want to do is to be able to reference, at point of sale a product portal specific to that customers pricing structure. Where my sales staff can identify the customer and the portal would list all products with the appropriate selling price.

My question is which would be better; to have one product table for all four pricing structures, or have four separate product tables for each pricing structure. In either scenario there would be a master product table holding other relevant data i.e. piece weight, skid quantities etc. For either scenario I need a little direction and assistance in how to build my relationships, and tables, I have gotten myself stuck. I’ve been taking some stabs in the dark, but now I think I have maybe made this more complicated then need be and sure could use some redirection. I know there is a lot more to be done to really make this a working solution, but if I could just get some success with the basic premise I may regain my focus.

which would be better; to have one product table for all four pricing structures, or have four separate product tables for each pricing structure

I think the answer is "C. None of the above". Rather, I would have one Products table, and one Prices table, where each Product would have 4 related records. A third table, PricingGroups, could be also useful - but not strictly required.

BTW, a portal listing some 3,000 records doesn't seem very practical.