locked
regarding db design RRS feed

  • Question

  • User655436493 posted

    This may not be an appropriate place to post this question, but I'm hoping I'll get a quicker response here.

    I'm working on a sql server database design for my asp.net web application

    I have a table with 500 products and for each product, prices will be changed dynamically for each new event created

    Method 1: Dynamically create a new column for storing the new event's price

    Method 2: copy the same 500 rows in another table having the event id as one more column.

                     So if another event is created, again 500 rows would be copied with respective event id


      If 100 events are created over time:

    For method 1: table will have 500 rows and 100 columns additional 

    For method 2: table will have the 50,000   rows and no additional columns


    Which method is better?? 


    Thursday, January 14, 2010 10:22 AM

Answers

  • User-126244515 posted

    I would create a table for the products and a table for the events, both having a primary key. Then create a third table, often referred to as an associated table, that contains two or three columns; product id and event id, and a primary key, if you don't use the product id and event id as a composite primary key.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 14, 2010 10:40 AM
  • User-952121411 posted

    I would do the following (and not be concerned so much about the number of rows, based on the example you provided):

    Table 1: Events

          Fields:   pkEventID, Name, Date, etc.

    Table 2: Products

          Fields:  pkProductID, fkEventID (only if products are event specific, otherwise omit this column), Description, etc.

    Table 3: ProductPricing

         Fields:  pkProductPricingID, fkProductID, fkEventID (can omit this column if it had to be defined in Products table), Price, etc.

    With this type structure (obviously not exact), your products table once built up may be mostly static (assuming the products are not event specific; which I do not think they are because you wanted to copy them over from event to event; no need to do that anymore).  Even if the table is not, you can just add new products as needed.  I recommend keeping just 1 record for a product, and allow the Pricing table to show the event specific pricing, rather than copying all those rows for every event.  Then all you need to do is add pricing information into the 'ProductPricing' table which holds the product and event ID. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 14, 2010 11:21 AM
  • User655436493 posted

    Here for Table 3, with each event I'll get 500 records of pricing right?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 14, 2010 11:42 AM
  • User-952121411 posted

    Here for Table 3, with each event I'll get 500 records of pricing right?
     

    Yes correct, but this is typical for a lookup table in your situation.  The important thing is that the Event and Products table do not get overly robust or repeated record entries.  Each of those tables has 1 meaningful record for each event or product respectively, and the pricing lookup table is on its own.

    The other point about the Pricing table is that you will only have a handful of columns and probably most if not all will be an 'int' or 'double' type that will not make the table get atrociously large.

    Regardless, lookup tables with often changing data will inevitably get big; that is one of the reasons why we use SQL Server because it can handle it!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 14, 2010 11:53 AM

All replies

  • User-126244515 posted

    I would create a table for the products and a table for the events, both having a primary key. Then create a third table, often referred to as an associated table, that contains two or three columns; product id and event id, and a primary key, if you don't use the product id and event id as a composite primary key.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 14, 2010 10:40 AM
  • User-952121411 posted

    I would do the following (and not be concerned so much about the number of rows, based on the example you provided):

    Table 1: Events

          Fields:   pkEventID, Name, Date, etc.

    Table 2: Products

          Fields:  pkProductID, fkEventID (only if products are event specific, otherwise omit this column), Description, etc.

    Table 3: ProductPricing

         Fields:  pkProductPricingID, fkProductID, fkEventID (can omit this column if it had to be defined in Products table), Price, etc.

    With this type structure (obviously not exact), your products table once built up may be mostly static (assuming the products are not event specific; which I do not think they are because you wanted to copy them over from event to event; no need to do that anymore).  Even if the table is not, you can just add new products as needed.  I recommend keeping just 1 record for a product, and allow the Pricing table to show the event specific pricing, rather than copying all those rows for every event.  Then all you need to do is add pricing information into the 'ProductPricing' table which holds the product and event ID. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 14, 2010 11:21 AM
  • User655436493 posted

    Here for Table 3, with each event I'll get 500 records of pricing right?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 14, 2010 11:42 AM
  • User-952121411 posted

    Here for Table 3, with each event I'll get 500 records of pricing right?
     

    Yes correct, but this is typical for a lookup table in your situation.  The important thing is that the Event and Products table do not get overly robust or repeated record entries.  Each of those tables has 1 meaningful record for each event or product respectively, and the pricing lookup table is on its own.

    The other point about the Pricing table is that you will only have a handful of columns and probably most if not all will be an 'int' or 'double' type that will not make the table get atrociously large.

    Regardless, lookup tables with often changing data will inevitably get big; that is one of the reasons why we use SQL Server because it can handle it!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 14, 2010 11:53 AM