DB Design Q. Hiding products by multiple rules

[Borvik]

I can muddle through some SQL, but I'm having some difficulty in wrapping my head on how I would design the following to be easy to query. This is a design question so all DB servers are applicable, I should be able to adapt anything to my server.

ProductList
-----------
ProductID
CategoryID
ManufacturerID
IsHidden
...etc

Products
------------
ProductID
CategoryID
ManufacturerID
...etc

Products and other related tables are imported daily from a third-party. To make some queries easier, and quicker I consolidate some of the data from multiple tables into the ProductList table using a view. Some of these table would contain rules for hiding products, though the way I'm doing it now is crude.

Currently I have these tables

hiddenProducts
--------------
ProductID

hiddenCategories
----------------
CategoryID

hiddenMfgs
----------
ManufacturerID

With this though, I can only hide individual products, all products in a category, or all products from a manufacturer. Currently this is done with a CASE statement and LEFT OUTER JOINs to the hide tables.

CASE
    WHEN (hp.productid IS NOT NULL OR hc.categoryid IS NOT NULL OR hm.manufacturerid IS NOT NULL) THEN 1
    ELSE 0
  END AS ishidden,

I want to be able to hide by more granular rules though:
Individual Products (this table would likely not change)
Manufacturer (not likely to change, but can)
Category (not likely to change, but can)
Manufacturer within category/categories (not to difficult to add)
Manufacturer except category/categories (this is the one I'm having difficulty with)

Precedence would be this:
1. Individual Products (overrides all else)
2. ??? (the rest are dependent on each other, and the design)

I'm trying not to create an over-abundance of tables, and maybe I'm over-thinking this too much.

What would be the best way to design this? Is a CASE statement like the one above really the best way to accomplish this?

Any recommendations are welcome.

Thanks.

 

[MrDataGuy]

First I did not read the posting in depth so this comment may not make sense. But what strikes me is the use of the hidden<> tables. Why not just have an additional field/column/attribute of your primary tables that is a hidden flag?

Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[Borvik]

Because the import scripts (provided by the third party) completely recreate the tables they populate, and I have no control over the data.

This is why I need secondary tables to tell which products to hide.

The view that I have populating my main table, is what populates my hidden flag. But if all I manipulated was a hidden flag, I would have to do it for every product individually rather than also allowing by manufacturer and category combinations. Additionally by allowing the manufacturer and category combinations, future products added to the import by the third party could automatically be hidden without any further input.

 

[Thargy]

Borvik,

if the tables are completely recreated that's not a problem.
Once the import has run, use an ALTER TABLE statement to add the flag you need.

If tables with existing data are being blown away, then that is a major problem, and should be stopped immediately. Data importing does NOT require the destruction of tables.

Regards

T

 

[Borvik]

Let me make the picture a little less blurry.

There are actually a total of 3 product tables: products, _products, and _productlisting. We subscribe to a data warehouse that provides product information and an import script for our database.

This import process imports the product data into a table it creates tproducts. If 'products' exists, then it is DROPPED, and finally tproducts is renamed products. This happens daily and I have ZERO leeway in changing the import process. I believe this is one of their ways of protecting their data, as we are paying for this service.

_products is a table for products we created, because they did not exist in the products table, or because they are unique to us and would never exist in the products table, this table DOES have a hidden flag.

While I could alter the products table to add a hidden flag, that flag would disappear every day and have to be recreated after every import. The hidden flag would then have to be remembered in another table. This also rules out the capability to automatically hide any NEW products that the data warehouse added that belong to a hidden category/manufacturer combination.

After the import process has been completed, it kicks off a script I wrote to combine the disparate data into a single usable dataset. It utilizes a VIEW to combine the data, so the SQL looks something like: INSERT INTO _productlisting SELECT * FROM COMBINED_PRODUCT_VIEW. My applications then query this _productlisting table to avoiding complicated joins making the UI more responsive.

 

[Thargy]

Borvik,

all that makes perfect sense.

Just create your COMBINED_PRODUCT_VIEW as the union of two queries. The one which queries the newly imported data should be something like

SELECT
    FIELD_1,
    FIELD_2
    NULL AS HIDDEN
  FROM IMPORTED_DATA
UNION
SELECT
    FIELD_1,
    FIELD_2,
    HIDDEN
  FROM BORVIKS_DATA

Use a null to make the union work (i.e. make the data sets match).


Regards

T

 

[Borvik]

That's how I have the view, my question is with regards how I might structure the "hidden" tables and how I might incorporate that into my view.

 

[Borvik]

Though I have some joins on the imported data to accommodate the "hides" that I have already implemented, using that CASE statement from earlier.

 

[Thargy]

Could you post the create table statements, so I can see precisely what you're up against?

Regards

T

 

[Borvik]

Here is what is currently in use:

The product tables

CREATE TABLE [dbo].[_productlist](
	[productid] [int] NOT NULL,
	[mfgpartno] [varchar](70) NULL,
	[categoryid] [int] NOT NULL,
	[productname] [varchar](500) NOT NULL,
	[productdescription] [varchar](500) NOT NULL,
	[manufacturerid] [int] NOT NULL,
	[mfgname] [varchar](60) NOT NULL,
	[creationdate] [datetime] NULL,
	[modifieddate] [datetime] NULL,
	[lastupdated] [datetime] NULL,
	[ishidden] [tinyint] NOT NULL,
	[cost] [numeric](18, 2) NULL,
	[vendorid] [varchar](60) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[_customproduct](
	[productid] [int] NOT NULL,
	[mfgpartno] [varchar](70) NOT NULL,
	[categoryid] [int] NOT NULL,
	[productname] [varchar](500) NOT NULL,
	[productdescription] [varchar](500) NOT NULL,
	[manufacturerid] [int] NOT NULL,
	[creationdate] [datetime] NOT NULL,
	[modifieddate] [datetime] NOT NULL CONSTRAINT [DF__customproduct_modifieddate]  DEFAULT (((1)/(1))/(1900)),
	[lastupdated] [datetime] NOT NULL,
	[ishidden] [tinyint] NOT NULL CONSTRAINT [DF__customproduct_ishidden]  DEFAULT ((0)),
 CONSTRAINT [PK__customproduct] PRIMARY KEY CLUSTERED 
(
	[productid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

-- The unalterable table for the third party
CREATE TABLE [dbo].[product](
	[productid] [int] NOT NULL DEFAULT ((0)),
	[manufacturerid] [int] NOT NULL DEFAULT ((0)),
	[isactive] [bit] NOT NULL DEFAULT ((1)),
	[mfgpartno] [varchar](70) NOT NULL DEFAULT (''),
	[categoryid] [int] NOT NULL DEFAULT ((0)),
	[isaccessory] [bit] NOT NULL DEFAULT ((0)),
	[equivalency] [float] NOT NULL DEFAULT ((0)),
	[creationdate] [datetime] NULL,
	[modifieddate] [datetime] NULL,
	[lastupdated] [datetime] NULL,
 CONSTRAINT [product_PK] PRIMARY KEY CLUSTERED 
(
	[productid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Hidden Tables (the isexclusive columns are currently not used)

CREATE TABLE [dbo].[_hiddencategories](
	[categoryid] [int] NOT NULL,
	[isexclusive] [tinyint] NOT NULL CONSTRAINT [DF__hiddencategories_isexculsive]  DEFAULT ((0))
) ON [PRIMARY]

CREATE TABLE [dbo].[_hiddenmanufacturers](
	[manufacturerid] [int] NOT NULL,
 CONSTRAINT [PK__hiddenmanufacturers] PRIMARY KEY CLUSTERED 
(
	[manufacturerid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[_hiddenproducts](
	[productid] [int] NOT NULL,
	[isexclusive] [tinyint] NOT NULL CONSTRAINT [DF__hiddenproducts_isexclusive]  DEFAULT ((0)),
 CONSTRAINT [PK__hiddenproducts] PRIMARY KEY CLUSTERED 
(
	[productid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

The View used to insert into _productlist.

CREATE VIEW [dbo].[v_productlisting]
AS
SELECT
  p.productid,
  p.mfgpartno,
  ci.custompartno,
  p.categoryid,
  COALESCE(d2.description, d1.description) AS productname,
  COALESCE(d3.description, d1.description) AS productdescription,
  p.manufacturerid,
  m.name AS mfgname,
  p.creationdate,
  p.modifieddate,
  p.lastupdated,
  c.contractid,
  ci.dateadded AS contractdateadded,
  0 AS instock, 
  0 AS stockcount, 
  0 AS hasstockcount,
  COALESCE(cc.cost, dc.cost) AS cost,
  COALESCE(cc.name, dc.name) AS vendorid,
  CASE ci.isprice 
    WHEN 1 THEN ci.pricemarkup 
    ELSE CEILING((COALESCE(cc.cost, dc.cost) * (1 + (COALESCE(ci.pricemarkup, cce.markup, c.defaultmarkup) / 100))) / 0.05) * 0.05 
  END AS realprice,
  CASE
    WHEN (hp.productid IS NOT NULL OR hc.categoryid IS NOT NULL) THEN 1
    ELSE 0
  END AS ishidden
FROM
  product p LEFT OUTER JOIN
  productdescriptions d1 ON p.productid = d1.productid AND d1.type = 1 LEFT OUTER JOIN
  productdescriptions d2 ON p.productid = d2.productid AND d2.type = 2 LEFT OUTER JOIN
  productdescriptions d3 ON p.productid = d3.productid AND d3.type = 3 LEFT OUTER JOIN
  _hiddenproducts hp ON p.productid = hp.productid LEFT OUTER JOIN
  _hiddencategories hc ON p.categoryid = hc.categoryid LEFT OUTER JOIN
  manufacturer m ON p.manufacturerid = m.manufacturerid CROSS JOIN
  _contracts c LEFT OUTER JOIN
  _contractcategorieseffective cce ON c.contractid = cce.contractid AND p.categoryid = cce.categoryid LEFT OUTER JOIN
  _contractitems ci ON c.contractid = ci.contractid AND p.productid = ci.productid LEFT OUTER JOIN
  _bestcost dc ON p.productid = dc.productid AND dc.pricegroupid = -1 LEFT OUTER JOIN
  _bestcost cc ON p.productid = cc.productid AND c.pricegroupid = cc.pricegroupid
WHERE 
  (cc.cost IS NOT NULL OR dc.cost IS NOT NULL)

 

[Borvik]

Oops, wrong view:

CREATE VIEW [dbo].[v_productlistbuilder]
AS
SELECT
  p.productid,
  p.mfgpartno,
  p.categoryid,
  COALESCE(d2.description, d1.description) AS productname,
  COALESCE(d3.description, d1.description) AS productdescription,
  p.manufacturerid,
  m.name AS mfgname,
  p.creationdate,
  p.modifieddate,
  p.lastupdated,
  CASE
    WHEN (hp.productid IS NOT NULL OR hc.categoryid IS NOT NULL OR hm.manufacturerid IS NOT NULL) THEN 1
    ELSE 0
  END AS ishidden,
  dc.cost,
  dc.name AS vendorid
FROM
  product p LEFT OUTER JOIN
  productdescriptions d1 ON p.productid = d1.productid AND d1.type = 1 LEFT OUTER JOIN
  productdescriptions d2 ON p.productid = d2.productid AND d2.type = 2 LEFT OUTER JOIN
  productdescriptions d3 ON p.productid = d3.productid AND d3.type = 3 LEFT OUTER JOIN
  _hiddenproducts hp ON p.productid = hp.productid LEFT OUTER JOIN
  _hiddencategories hc ON p.categoryid = hc.categoryid LEFT OUTER JOIN
  _hiddenmanufacturers hm ON p.manufacturerid = hm.manufacturerid LEFT OUTER JOIN
  manufacturer m ON p.manufacturerid = m.manufacturerid LEFT OUTER JOIN
  _bestcost dc ON p.productid = dc.productid AND dc.pricegroupid = -1
UNION ALL
SELECT
  cp.productid,
  cp.mfgpartno,
  cp.categoryid,
  cp.productname,
  cp.productdescription,
  cp.manufacturerid,
  COALESCE(m.name, m2.mfgname) AS mfgname,
  cp.creationdate,
  cp.modifieddate,
  cp.lastupdated,
  cp.ishidden,
  dc.cost,
  dc.name AS vendorid
FROM
  _customproduct cp LEFT OUTER JOIN
  manufacturer m ON cp.manufacturerid = m.manufacturerid LEFT OUTER JOIN
  _custommfgs m2 ON cp.manufacturerid = m2.manufacturerid LEFT OUTER JOIN
  _bestcost dc ON cp.productid = dc.productid AND dc.pricegroupid = -1