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

[DevelopV]

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?

 

[PHV]

I'd use a table of allowed product types per customer.
Then in the order form you may use a combo based on this table joined to the product table and filtered with the current customer.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DevelopV]

Hi PHV

Thanks

Could I do it another way: Allocate product types to a customer that they cannot order
Most customers will be allowed to order all product types, so this would reduce the amount of admin on customers.

 

[PHV]

Do it either way: an Allow table or a Deny table ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886