locked
How to Normalize the table structure RRS feed

  • Question

  • Hi,

        I've a table structure like as below. There are some problem to Normalize the table structure. Because according to the below image there are so many product available in my table but I took a "Stationary" as sample. The stationary product has multiple product code. The product code further classfied by sub code. Each sub code has a unique description value.

    Anyone can help to normalize the table.

    • Changed type BaskaranR Wednesday, March 12, 2014 5:33 AM
    Tuesday, March 11, 2014 2:00 PM

Answers

All replies

  • Have a table Products,

    Have a table Products_Codes (Prodid, prodcode....)

    Have a table Product_Subcode (prodcode, prodsubcode, description...)

    Does the prodcode have may subcodes?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Kalman Toth Saturday, March 15, 2014 7:54 AM
    • Marked as answer by Fanny Liu Tuesday, March 25, 2014 10:51 AM
    Wednesday, March 12, 2014 5:49 AM
  • Hi,

        Thanks for the reply...!

     Some product code has multiple sub code and some others not having the sub code.

    For ex: If the product code has multiple subcode then the subcode starts with 1 else 0.

    Wednesday, March 12, 2014 6:15 AM
  • Why not having product code table and sub code table with one -to -many relationship....Why do need 0 or 1?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, March 12, 2014 12:30 PM
  •      We may do as per your suggestion. But the main concern is there is no limit for subcode. Due to this we may insert more than 3000 rows per product. I feel this is not good to do this.

    Do you have any other suggestion to handle limited rows to insert per product?

    Wednesday, March 12, 2014 12:40 PM
  • Why do you want to limit? For example we have some products  with more than 500 categories  and if you have properly created indexes the queries run quickly .

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, March 12, 2014 1:21 PM
  •          Above mentioned data are sample not showing my orginal data. There is no limitation for product.   Actually the problem is some of the product has 7500 to 8000 rows. Simply if I create 100 product then the table rows increased as 8Lak rows. According to this my current table size is 12crs for 10K product. So when I do the join with other table for single user it is working fine but simultaneously more than 10 user accessing by join its getting low performance. So that I decided to change the architect for this table.

    Any Idea?

    Wednesday, March 12, 2014 1:43 PM
  • >>>rows increased as 8Lak rows

    Nowadays that is not a  problem, you just need to have properly defined indexes to speed up the queries... BTW select does not block readers, why do you think many users get low performance? Can you show us the typical query ?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Friday, March 14, 2014 10:36 AM
  • I completely agree with Uri. Three tables (guessing on more descriptive names)

    ProductLine (ProductLineId PK)

    Product ((ProductLineId, ProductId, (PK (ProductLineId, ProductCodeId) )

    ProductStyle ((ProductLineId, ProductId, ProductStyle), (PK (ProductLineId, ProductId, ProductStyle))

    And attach the attributes at each level so no duplication of data..

    Product style is probably more of a ProductSKU, in that it is probably the product that you are gong to sell, and instead of composite PKs you might change to use surrogate keys, but 800K rows wouldn't be a big deal for such a design (since if you can afford to sell that many products you can buy a nice server!)


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    Wednesday, March 19, 2014 6:55 PM