locked
Extending design to support item variant with additional columns RRS feed

  • Question

  • I have a table of products:

    ProductId int
    Description varchar(256)
    Width numeric(9,4)
    Height numeric(9,4)
    . . .

    There are 23 additional columns mostly of type int, numeric(9,4) and bit with a handful of varchar columns of width 10 or less.

    Now I want to add support for a more advanced set of products. The original columns still apply but the more advanced product line has 23 additional columns of spec. data. As with the original columns the new columns are all int, numeric(9,4), bit and small varchar columns.

    I could simply add the 23 columns to the existing table. I could then add a ProductTypeID column indicating whether a record is the original type or the new enhanced type. The new columns would have null values for the 4,000 original product records.

    Another possibility is to create a second table with just the new columns and then add a foreign key column to the original table.

    ProductId int
    AdvancedProductSpecId int -- a null value implies the original product type

    A third possibility is to leave the original table unchanged and create a second table with both the original and the new columns. Then there would be no need to have a foreign key but there would be design redundancy in that one table would have a subset of columns of the other.

    There are currently about 4,000 records in the table representing the original products. For the new enhanced products there will initially be a few hundred records. The total number of product records will probably never exceed 10,000 over the life of the database. So whichever design I choose the number of records involved and the size of each record are small enough that conserving storage space and performance will be relatively low importance versus other considerations.

    Most of the time, access to data will be via stored procedures supporting standard CRUD operations. From the standpoint of best practices with respect to database design does one of the three options stand out as being the best choice?


    • Edited by Kalman Toth Tuesday, January 14, 2014 7:27 AM spelling
    Thursday, January 9, 2014 9:20 PM

Answers

  • dihedral,

    I'd say its better to go with option 2.

    Create a AdvancedProduct_Spec table that contains the extra 23 columns + 1 column which is the product ID - foreign key of the base table (that has the initial set of info for this advanced product). and also the primary key of this secondary table. That should suffice your requirement.

    Anyways there wont be more than one record per advanced product right ? Y the need of an unnecessary extra column ? :)



    Thanks, Jay <If the post was helpful mark as 'Helpful and if the post answered your query, mark as 'Answered'>

    • Marked as answer by dihedral Tuesday, January 14, 2014 1:01 AM
    Friday, January 10, 2014 2:35 AM

All replies

  • dihedral,

    I'd say its better to go with option 2.

    Create a AdvancedProduct_Spec table that contains the extra 23 columns + 1 column which is the product ID - foreign key of the base table (that has the initial set of info for this advanced product). and also the primary key of this secondary table. That should suffice your requirement.

    Anyways there wont be more than one record per advanced product right ? Y the need of an unnecessary extra column ? :)



    Thanks, Jay <If the post was helpful mark as 'Helpful and if the post answered your query, mark as 'Answered'>

    • Marked as answer by dihedral Tuesday, January 14, 2014 1:01 AM
    Friday, January 10, 2014 2:35 AM
  • Whats the chance of all 23 additional columns to contain value always? If there's very little chance of all columns having values then what you could is a modified format of 2nd option

    ie. have a separate table with three columns

    1. FK of main table
    2. ValueType (corresponds to 23 columns as values)
    3. ActualValue

    This would be generic and flexible as whenever you want to add a new attributetype you can just add it as a new column with type value as new attribute value and then its associated value. 

    Another approach is to keep them as 23 columns itself along with FK column but making columns as SPARSE so that storage is optimized. Also to make DML operations into these columns easy you may also create a COLUMNSET including all of them

    see

    http://visakhm.blogspot.in/2010/03/sparse-columns-and-column-sets-in-sql.html


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, January 10, 2014 4:17 AM
  • You are right about having the foreign key field double as the primary key field. Thanks for the tip.
    Tuesday, January 14, 2014 1:03 AM
  • Thanks for the suggestion. Most of the columns will have values. If I ever need a more design for more sparse data I will use a design like the one you proposed.
    Tuesday, January 14, 2014 1:05 AM
  • >You are right about having the foreign key field double as the primary key field.

    That is how 1:1 relationship can be implemented.

    The second table PK is FK to the first table (main).


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Tuesday, January 14, 2014 7:26 AM