×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

DB Design Q. Hiding products by multiple rules

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.

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,

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

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
www.LionCrestSoftwareServices.com

 

RE: DB Design Q. Hiding products by multiple rules

(OP)
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.

RE: DB Design Q. Hiding products by multiple rules

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

RE: DB Design Q. Hiding products by multiple rules

(OP)
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.

RE: DB Design Q. Hiding products by multiple rules

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

CODE

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

RE: DB Design Q. Hiding products by multiple rules

(OP)
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.

RE: DB Design Q. Hiding products by multiple rules

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

RE: DB Design Q. Hiding products by multiple rules

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

Regards

T

RE: DB Design Q. Hiding products by multiple rules

(OP)
Here is what is currently in use:

The product tables

CODE

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)

CODE

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.

CODE

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)

RE: DB Design Q. Hiding products by multiple rules

(OP)
Oops, wrong view:

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close