Data Integrity Across Tables - Please evaluate.

Results 1 to 2 of 2

Thread: Data Integrity Across Tables - Please evaluate.

  1. #1
    Join Date
    Dec 1969

    Default Data Integrity Across Tables - Please evaluate.

    Hi,<BR><BR>I have the following situation (I&#039;ve been over this before so sorry for the repetition)<BR><BR>Products with attributes. e.g. colour, size.<BR><BR>The attributes have options. e.g. green, red.<BR><BR>The options form combinations which can be priced seperately.<BR><BR>e.g. red/green = £3<BR><BR>I figure that a good design would put each entity in its own table.<BR><BR>A table for products, attributes, options and combinations.<BR><BR>The problem is that combinations directly derive/correspond from options. So if a stored procedure is reading options and then a concurrent process changes the options and thus the combinations, the stored procedure doing the reading will then go on to read combinations which are out of synch with the options it read.<BR><BR>So my idea is to put all the data into a single table and then, using a locking hint, place a shared lock on this table when the data is being read.<BR><BR>Each row in this table is capable of storing all of the different types of data - attributes, options and combinations. There is also a flag which marks what type of data is being stored in the row - much like variables in a scripting language.<BR><BR>What do you think of this design?<BR><BR>Also, if there are many processes reading from this table and locking it, will an update be able to get to the data?<BR><BR>Cheers,<BR><BR>I.<BR><BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: Data Integrity Across Tables - Please evaluate

    Well, how about thinking it over a bit, and ask yourself a few questions?<BR>Try and figure out how many combinations you will have?<BR>And if you come along later and add another option, how do you plan to rip through and make up all the new combinations and pricing to acomidate just 1 more option?<BR><BR>When you have a price change on just 1 option, how will you rip through and do an update?<BR>AND<BR>How will you do it so this price update doesnt affect "Past Records/Orders"?<BR><BR>I would probably do a <BR>Products table <BR>with a discount field for options quantity?<BR>1 - 4 options 10%, 5 - 10 options 15% that idea<BR><BR>Options table<BR><BR>and a Price table ID, OptionID, pPrice, prDate, bolUseThis<BR><BR>prDate = when the new price was made<BR>bolUseThis = a flag, if true is current price (All others false)<BR><BR>For every price change just add a new record <BR><BR>When you build an order and use the price ID, then the price that was sold back 10 years ago will still show <BR><BR>Im just blabbing<BR>but im sure you can see how what you are thinking of, might be a bit complicted to work with no?<BR><BR>Only my 2 cents.<BR><BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts