DB Design Q. Hiding products by multiple rules
DB Design Q. Hiding products by multiple rules
(OP)
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.
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.
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.
CODE
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,
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.
RE: DB Design Q. Hiding products by multiple rules
Lion Crest Software Services
Anthony L. Testi
President
www.LionCrestSoftwareServices.com
RE: DB Design Q. Hiding products by multiple rules
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.
RE: DB Design Q. Hiding products by multiple rules
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
RE: DB Design Q. Hiding products by multiple rules
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.
RE: DB Design Q. Hiding products by multiple rules
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
CODE
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
RE: DB Design Q. Hiding products by multiple rules
RE: DB Design Q. Hiding products by multiple rules
RE: DB Design Q. Hiding products by multiple rules
Regards
T
RE: DB Design Q. Hiding products by multiple rules
The product tables
CODE
[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)
CODE
[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.
CODE
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)
RE: DB Design Q. Hiding products by multiple rules
CODE
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