Database design question; Limit records via table design or a query or both

[TimTDP]

I have the tables: "tblProduct", "tblProductType" & "tblCustomer"
Tables "tblProduct" & "tblProductType" have a one-many relationship, using the field "ProductId"

On a Customer Order form I want to limit the type of products a customer can order.
for example, Customer:
* ABC can order ALL products, irrespective of the product type
* HIJ can order products belonging to product type Furniture
* XYZ can order products belonging to product types Furniture, Pottery & Garden

I am unsure how to achieve this so an help will be greatly appreciated.
Is this done during table design or via a query or a combination of both?

 

[Isskint]

hi Tim

I would suggest creating a table of Product Restrictions. This table would list which products a customer can not order. So you would have:

RestID - AutoNumber (PK)
CustID - Integer (FK)
ProdID - Integer (FK)

Create a query to give you a valid product list by customer (using a DLOOKUP criteria to this Restriction table to identify valid products).

At the point of creating a customer order, you would use this new query to provide a list (Combo box) of valid products for the current customer.

If your list of Restricted products is HUGE, you could always do this the other way around - a Valid Product table.

I will try to design a demo DB later to make this clearer.

 

[jdraw]

A few thoughts while reading your post:

If you have a table - to identify ProdTypesForCustomer

tblProdTypesForCustomer
CustId
ProdTypeId

(a)any given CustID could have 1 or more records in this table based on the ProdTypeId you have set for that Customer
b) you have to set up these criteria before CustId selects products,
c) CustId + ProdTypeId form a composite PK in this table)

When you offer a list of Products for a Customer to Choose, you filter the Products
to those Products that have ProdTypeId in tblProdTypesForCustomer where the CustomerId = CustId in the tblProdTypesForCustomer table.

OOOoops: I see Isskint has responded while i was typing. Our tables are similar (restrictions), and limited list of products available to Customer. He has assigned RestId as surrogate PK which is fine. In either case the CustId and ProdId relate back(FK) to Customer and product tables.

 

[Isskint]

Well its very basic but here is the demo.

frmNewOrder will present you a list of valid ProductIDs for the selected customer.

You will need an interface to maintain the list of restricted products and that is probably best done through a subform on a customer details type form. see frmCustomers.