|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Torn over a normalizing decisionI'm still cleaning up this old app with its accompanying database. I've
found an issue that I'm really torn about whether or not to normalize. I have a field in many of the pricing tables for the pricing method. At this time, each pricing method simply gets a text entry from the program, i.e., "per unit", "per sq. ft." and so forth. But I've went through all of the tables and discovered there are really only 8 pricing methods. I could easily normalize this to a "PricingMethods" table, then use a foreign key to reference the applicable pricing method. But here's the rub. I've went thru the code, and the net effect of normalizing this table will simply be to add about 100 JOINS to the SQL code. Absolutely nothing will be improved -- except for the small amount of redundant data using an actual text entry versus a FK back to a PricingMethod table. On the other hand, complexity will certainly rise. Thoughts, flames, etc. welcome. It isn't obvious from your narrative why normalization would require you to
replace the PricingMethod column in the pricing tables. Couldn't you just create PricingMethods and add a foreign key to the existing column? Or add a CHECK to constrain the column to the 8 possible values? On the other hand if you mean that there is a transitive dependency involved then there surely would be a benefit in removing the column altogether. > I have a field in many of the pricing tables So why do you have more than one pricing table? There's apparently at least one common attribute (pricing method) so why isn't there a common table. Sounds like that's the real design issue. -- David Portas SQL Server MVP -- Thanks for your response David.
The pricing program creates a custom-configured item (I'd rather not disclose that, but the similarity is enough, so let's just say it's a PC). Separate entities but each have pricing methods. For example, to create a custom item, the processor costs have different pricing methods depending upon which manufacturer they come from (for example, Intel has different pricing methods than AMD ). Ditto for the case, etc. The CHECK constrain is a good idea :=) Show quote "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:q4udnRuu08PqO67eRVn-1g@giganews.com... > It isn't obvious from your narrative why normalization would require you > to replace the PricingMethod column in the pricing tables. Couldn't you > just create PricingMethods and add a foreign key to the existing column? > Or add a CHECK to constrain the column to the 8 possible values? On the > other hand if you mean that there is a transitive dependency involved then > there surely would be a benefit in removing the column altogether. > >> I have a field in many of the pricing tables > > So why do you have more than one pricing table? There's apparently at > least one common attribute (pricing method) so why isn't there a common > table. Sounds like that's the real design issue. > > -- > David Portas > SQL Server MVP > -- > > None of what you have said explains why there is more than one pricing table
with the same column(s)... Glad I helped anyway. -- David Portas SQL Server MVP -- Well, I'll try again ...
Using our PC analogy, a laptop uses different components than our desktops. Our desktop doesn't have a screen attached to it for example, but our laptop will. So if we were to custom-configure a laptop, we will need our pricing code to access the components that are required for a laptop, but if we are making a desktop tower, we will want to access those components instead. It's certainly true that we can create common tables for things like hard drives and processors. So our entity structure is: Computers Laptops Desktops PDAs etc. consisting of components: Hard drives Cases Processors Screens screens have various characterisics such as size, whether LCD, pixels, etc., but they are based upon first of all, whether the computer is a laptop, so our table might look like this: ScreenID ComputerTypeID(FK) ScreenType ScreenPixels Cost PricingMethod now processors have fans and heat sinks, custom-made by processor size and manufacturer, so here might be a sample heat sink table: HeatSinkID ProcessorID(FK) HeatSink Cost PricingMethod Now I see no way -- despite the fact that they both have two common columns -- that these tables can be combined. Show quote "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:uhroyDBwFHA.2312@TK2MSFTNGP14.phx.gbl... > None of what you have said explains why there is more than one pricing > table with the same column(s)... > > Glad I helped anyway. > > -- > David Portas > SQL Server MVP > -- > > See the following example. You may or may not need "component_type"
depending on whether part_no is to be unique across all types or not. Try not to split attributes across tables. Your code will be much simpler if you keep them in one place only. CREATE TABLE components (part_no INTEGER NOT NULL PRIMARY KEY, component_type CHAR(1) NOT NULL CHECK (component_type IN ('S','H' /* .... */)), cost NUMERIC(10,2) NOT NULL, pricing_method VARCHAR(10) NOT NULL /* CHECK (pricing_method IN (...)) */, UNIQUE (component_type, part_no)) ; CREATE TABLE screens (part_no INTEGER NOT NULL PRIMARY KEY, component_type CHAR(1) DEFAULT 'D' NOT NULL CHECK (component_type ='S'), screen_pixels INTEGER NOT NULL, FOREIGN KEY (component_type, part_no) REFERENCES components (component_type, part_no)) ; CREATE TABLE heat_sinks (part_no INTEGER NOT NULL PRIMARY KEY, component_type CHAR(1) DEFAULT 'D' NOT NULL CHECK (component_type ='S'), processor_id INTEGER NOT NULL /* REFERENCES processors (processor_id) */, FOREIGN KEY (component_type, part_no) REFERENCES components (component_type, part_no)) ; -- David Portas SQL Server MVP -- Oops. My constraints got a bit confused. Here's a correction:
CREATE TABLE components (part_no INTEGER NOT NULL PRIMARY KEY, component_type CHAR(1) NOT NULL CHECK (component_type IN ('S','H' /* .... */)), cost NUMERIC(10,2) NOT NULL, pricing_method VARCHAR(10) NOT NULL /* CHECK (pricing_method IN (...)) */, UNIQUE (component_type, part_no)) ; CREATE TABLE screens (part_no INTEGER NOT NULL PRIMARY KEY, component_type CHAR(1) DEFAULT 'S' NOT NULL CHECK (component_type ='S'), screen_pixels INTEGER NOT NULL, FOREIGN KEY (component_type, part_no) REFERENCES components (component_type, part_no)) ; CREATE TABLE heat_sinks (part_no INTEGER NOT NULL PRIMARY KEY, component_type CHAR(1) DEFAULT 'H' NOT NULL CHECK (component_type ='H'), processor_id INTEGER NOT NULL /* REFERENCES processors (processor_id) */, FOREIGN KEY (component_type, part_no) REFERENCES components (component_type, part_no)) ; -- David Portas SQL Server MVP -- Thanks much for your insight and advice David. I'm going to mull this over
before I proceed further with my design. Show quote "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:1127468185.689279.63590@f14g2000cwb.googlegroups.com... > Oops. My constraints got a bit confused. Here's a correction: > > CREATE TABLE components (part_no INTEGER NOT NULL PRIMARY KEY, > component_type CHAR(1) NOT NULL CHECK (component_type IN ('S','H' /* > ... */)), cost NUMERIC(10,2) NOT NULL, pricing_method VARCHAR(10) NOT > NULL /* CHECK (pricing_method IN (...)) */, UNIQUE (component_type, > part_no)) ; > > CREATE TABLE screens (part_no INTEGER NOT NULL PRIMARY KEY, > component_type CHAR(1) DEFAULT 'S' NOT NULL CHECK (component_type > ='S'), screen_pixels INTEGER NOT NULL, FOREIGN KEY (component_type, > part_no) REFERENCES components (component_type, part_no)) ; > > CREATE TABLE heat_sinks (part_no INTEGER NOT NULL PRIMARY KEY, > component_type CHAR(1) DEFAULT 'H' NOT NULL CHECK (component_type > ='H'), processor_id INTEGER NOT NULL /* REFERENCES processors > (processor_id) */, FOREIGN KEY (component_type, part_no) REFERENCES > components (component_type, part_no)) ; > > -- > David Portas > SQL Server MVP > -- > I think what troubles me is the fanout of attributes for the lower level
components. I was only using the PC as an analogy -- in this particular case, a particular component may have 100 different patterns for a particular option, with individual pricing for each pattern. So it seems to me that you have to have a "pattern pricing" table also. Show quote "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:1127468185.689279.63590@f14g2000cwb.googlegroups.com... > Oops. My constraints got a bit confused. Here's a correction: > > CREATE TABLE components (part_no INTEGER NOT NULL PRIMARY KEY, > component_type CHAR(1) NOT NULL CHECK (component_type IN ('S','H' /* > ... */)), cost NUMERIC(10,2) NOT NULL, pricing_method VARCHAR(10) NOT > NULL /* CHECK (pricing_method IN (...)) */, UNIQUE (component_type, > part_no)) ; > > CREATE TABLE screens (part_no INTEGER NOT NULL PRIMARY KEY, > component_type CHAR(1) DEFAULT 'S' NOT NULL CHECK (component_type > ='S'), screen_pixels INTEGER NOT NULL, FOREIGN KEY (component_type, > part_no) REFERENCES components (component_type, part_no)) ; > > CREATE TABLE heat_sinks (part_no INTEGER NOT NULL PRIMARY KEY, > component_type CHAR(1) DEFAULT 'H' NOT NULL CHECK (component_type > ='H'), processor_id INTEGER NOT NULL /* REFERENCES processors > (processor_id) */, FOREIGN KEY (component_type, part_no) REFERENCES > components (component_type, part_no)) ; > > -- > David Portas > SQL Server MVP > -- > hi earl,
use an view instead of joins. have an instead of update,insert and delete triggers on the view In that way you can normalize without affecting the front end -- Show quotethanks, ------------------------------------ Jose de Jesus Jr. Mcp,Mcdba Data Architect Sykes Asia (Manila philippines) MCP #2324787 "Earl" wrote: > I'm still cleaning up this old app with its accompanying database. I've > found an issue that I'm really torn about whether or not to normalize. > > I have a field in many of the pricing tables for the pricing method. At this > time, each pricing method simply gets a text entry from the program, i.e., > "per unit", "per sq. ft." and so forth. But I've went through all of the > tables and discovered there are really only 8 pricing methods. I could > easily normalize this to a "PricingMethods" table, then use a foreign key to > reference the applicable pricing method. > > But here's the rub. I've went thru the code, and the net effect of > normalizing this table will simply be to add about 100 JOINS to the SQL > code. Absolutely nothing will be improved -- except for the small amount of > redundant data using an actual text entry versus a FK back to a > PricingMethod table. On the other hand, complexity will certainly rise. > > Thoughts, flames, etc. welcome. > > > Thanks Jose, I will look into that approach.
Show quote "Jose G. de Jesus Jr MCP, MCDBA" <Email me> wrote in message news:B3535F3E-CB72-4F4B-95A9-6A17E8AF6403@microsoft.com... > hi earl, > > use an view instead of joins. > have an instead of update,insert and > delete triggers on the view > > In that way you can normalize without affecting the front end > > -- > thanks, > > ------------------------------------ > Jose de Jesus Jr. Mcp,Mcdba > Data Architect > Sykes Asia (Manila philippines) > MCP #2324787 > > > "Earl" wrote: > >> I'm still cleaning up this old app with its accompanying database. I've >> found an issue that I'm really torn about whether or not to normalize. >> >> I have a field in many of the pricing tables for the pricing method. At >> this >> time, each pricing method simply gets a text entry from the program, >> i.e., >> "per unit", "per sq. ft." and so forth. But I've went through all of the >> tables and discovered there are really only 8 pricing methods. I could >> easily normalize this to a "PricingMethods" table, then use a foreign key >> to >> reference the applicable pricing method. >> >> But here's the rub. I've went thru the code, and the net effect of >> normalizing this table will simply be to add about 100 JOINS to the SQL >> code. Absolutely nothing will be improved -- except for the small amount >> of >> redundant data using an actual text entry versus a FK back to a >> PricingMethod table. On the other hand, complexity will certainly rise. >> >> Thoughts, flames, etc. welcome. >> >> >> Get a copy of TREES & HIERARCHIES IN SQL and look at some of the parts
explosion models.  |
|||||||||||||||||||||||
Other interesting topics