How to classify Products in tree table with three level of classifications

Question

I am building c# project for sales management

I made table to register orders (selling items) and table to register items came in into my warehouse

as you know in stores for example you may sell LED screen with 50 Inch or LED screen with 32 Inch or what every the screen specs it is or what ever the product it is

now if I want to know how many screens I sell and how many screens still in my warehouse for specific day, week,month or year no matter what the screen specs it is (size, type LED LCD )

so I want to make classifications for the products in new table (Product_Classification) on three levels with meaningful identifier and then include that meaningful identifier within these tables so I am able to query any statistical info I want for example

how many screen I selled in a day, week,month or year? how many screen with size 50 Inch I selled in a day, week,month or year?... etc

I planning to format that identifier and divided it into 3 parts with 4 digits in each part and each part linked to a level in that tree XXXX-XXXX-XXXX

example 1

PK_ID |    Full_identifier     | identifier_Description       | class1_ID | class1_Description | class2_ID | class2_Description | class3_ID | class3_Description 
  1   | 1122                   | electronic devices           |           |                    |           |                    |           |     
  2   | 11223344               | electronic devices           |   3344    | LED screen         |           |                    |           |     
  3   | 112233445566           | electronic devices           |   3344    | LED screen         |   5566    |   32 Inch          |           |     
  4   | 1122334455668899       | electronic devices           |   3344    | LED screen         |   5566    |   32 Inch          |    8899   |     Sony Brand

advantage of this Full_identifier :

as you can see each 4 digits of the Full_identifier will allow me to access a level on that classification like I can query all electronic devices by using first part of the Full_identifier 1122 or query all LED screen by using first two parts of the Full_identifier 11223344
another reason to implement it, when a user want to register a product in warehouse from UI he doesn't have to select 4 categories to categorize the product, at my UI there is only one dropdown list which will be built at run time from this table Product_Classification with auto-complete when user start typing a list of nested available categorize will appear and when he choose a category then the Full_identifier will be stored in required column so whenever I need any sub-category in the tree table I can access it right away by substring the Full_identifier according to chosen level by user

for statistical reports if I want to list all LED screen I can simply do by using 3344 the second part of the Full_identifier as below example

> SELECT Full_identifier,
>           identifier_Description
>        || ' - '
>        || class1_Description
>        || ' '
>        || class2_Description
>        || ' '
>        || class3_Description
>           AS PRODUCT_Classification   FROM PRODUCT_CLASSIFICATION  WHERE Full_identifier = 3344

if I want to list all Sony Brand products I can simply do by using 8899 the second part of the Full_identifier as below example

SELECT Full_identifier,
          identifier_Description
       || ' - '
       || class1_Description
       || ' '
       || class2_Description
       || ' '
       || class3_Description
          AS PRODUCT_Classification   FROM PRODUCT_CLASSIFICATION  WHERE Full_identifier = 8899

questions

How to manage that identifier if I used sequence for each level class1_ID which will be incremented only if the class1_Description not changed

I can make that identifier unique but not PK as recommended here

Is that Possible and good implemented ?

@BobC as I write at my question each 4 digits of the Full_identifier will allow me to access a level on that classification I can query all electronic devices by using first part of the Full_identifier 1122 or query all LED screen by using first two parts of the Full_identifier 11223344 — sam, Mar 10 '17 at 13:30
no its how to build column full identifier my friend how to generate that column? how to controlled? — sam, Mar 10 '17 at 22:35
This is a duplicate. Continuation of the discussion of why this is a bad idea. http://stackoverflow.com/questions/42704445/what-is-the-correct-way-to-implement-controlled-primary-key-depending-on-column — unleashed, Mar 13 '17 at 16:17

score 0 · Answer 1 · answered Mar 10 '17 at 22:41

0

To answer you specific questions:

Is that Possible? Yes good implemented? No.

Multi-parts or "smart" keys are not a good idea. Any query now requires substringing or decoding. And how does the user specify the level that he or she wants? In fact, looking at you table design, you already have the attributes that describe the product, and a PK. So I see no use for this.

answered Mar 10 '17 at 22:41

BobC

4,208
1
12
15

hmm... then why they use something like that with passport and social security number? and what is thr good implementation for that? – sam Mar 10 '17 at 23:15
I'm not sure I understand your comment? Who is "they"? And why would I substring my passport number? – BobC Mar 10 '17 at 23:19
in general most of countries their passport number has meaning and also the national security number – sam Mar 10 '17 at 23:21
In healthcare, drugs have a national drug identifier called an NDC. It is 11-digits in total, but is actually broken into 3 parts. And it is well known to the humans in the industry as its value like 12345-6789-11 – sam Mar 10 '17 at 23:26
A few things come to mind. These numbers and their formats were probably invented before relational databases. So these are legacy from the days of COBOL and ISAM (or maybe even older). In a relational database, it does not make any sense to do this. In your example, if you want to query class1_ID=3344, then write "select blah where class1_ID=3344". Simple. Or perhaps even better, "select blah where class1_Description='LED Screen' " Simple and easy to understand. You have yet to give a good reason to implement what you suggest. If you don't believe me, try cross posting to Data-Warehouse... – BobC Mar 10 '17 at 23:29
NO NO my friend I believe you my friend... I am just Asking because something exist in real life and It is help me within my project if it is applicable or there is better why and another reason to implement I have update my post with small screenshot please if kindly take a look – sam Mar 11 '17 at 14:35
Thanks for your help and discussing this with me I really appreciate sharing your experience – sam Mar 11 '17 at 14:43
Ok looking at your updated screenshot. Let's say the user wants to find 'Sony Brand'. What does your query look like? – BobC Mar 11 '17 at 14:59
@it will look like that `SELECT Full_identifier, identifier_Description|| ' - '|| class1_Description || ' '|| class2_Description || ' '|| class3_Description AS PRODUCT_Classification FROM PRODUCT_CLASSIFICATION WHERE Full_identifier=1122334455668899` – sam Mar 11 '17 at 15:10
Two things. 1. How does the user know to put in 1122334455668899? 2. That only brings back on product; the user wants *all*' Sony Brands. Perhaps 5555444433338899, and dozens more... – BobC Mar 11 '17 at 15:17
he would not know 1122334455668899 and will never deal with it... when he is going to register product from UI warehouse and choose using dropdown list the category what actually happen is that I am showing the user the description of the category and at background I will store the Full_identifier of category 1122334455668899 and whenevery want to move up or down through the category for any reason I can use that Full_identifier to move it up or down – sam Mar 11 '17 at 15:33
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/137831/discussion-between-bobc-and-sam). – BobC Mar 11 '17 at 15:35
continuing the comment... and that will brings back all Sony Brands product no mater if it is screen or camera or mobile because all Sony Brands product has the forth part of Full_identifier which is 8899 – sam Mar 11 '17 at 15:37

How to classify Products in tree table with three level of classifications

1 Answers1