Database design question

• Jeff Moden

  SSC Guru

  Points: 1000539

  More actions

  January 23, 2012 at 7:43 pm

  #1437450

  SQLKnowItAll (1/23/2012)

  ---

  SQLKicksButt (1/23/2012)

  ---

  Jared, you are correct that there could be different implications for adding a new appliance versus just updating. I would say adding a new appliance is a rare thing for us (maybe once a year) but when it does happen its a mini-project in its impact (at least two weeks of work). But adding or changing existing properties for an appliance is pretty much monthly. This system is web based using ASP.NET. I would have to create a new interface to handle the catalog of the properties table that is used by all appliances. I would also have to create a new interface to add, update, delete the appliance and that interface would also handle the property associations to the appliance.

  So that's where I have some problems with this approach. When you submit a form via asp.net to SQL Server for an update, I assume you are updating several properties at once.

  So instead of:

  UPDATE refrigeratorProperties

  SET columnA = @columnA, columnb = @columnb, ...

  WHERE applianceID = 1234

  You will now have:

  UPDATE properties

  SET value = @valuea

  WHERE propertyid = 1 AND applianceID = 1234;

  UPDATE ...;

  UPDATE ...;

  To me the several updates seems to be a lot more work and will probably be more resource intensive. Not to mention joins to the catalog and such. I don't know, to me it would be less maintenance and better performance of the application to separate the properties of the different classes where they are not uniform across all classes. Just my opinion, but I wouldn't want to have to code all of those update statements separately.

  I would say that once the properties of an appliance make and model have been inserted (a pretty easy thing to do with a CSV input and a decent splitter), that few if any properties would ever be updated. A new model might come out but inserting 40 or so new rows using a splitter or even XML wouldn't be a problem.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
  "Change is inevitable... change for the better is not".
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  Intro to Tally Tables and Functions

• Evil Kraig F

  SSC Guru

  Points: 100851

  More actions

  January 23, 2012 at 9:39 pm

  #1437460

  Sounds a lot like the information description storage for something like CNet, or any other massive product review database. There's some free to try ones out there that you can get your hands on sample copies of for a reference.

  Attribute/column anything you'll need to do calculations or grouping on (price comes to mind as a gimme, number in warehouse as another), vertically store anything that's merely used as description.

  ---

  - Craig Farrell

  Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

  For better assistance in answering your questions[/url] | Forum Netiquette
  For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
  Twitter: @AnyWayDBA