Yes, I think it's a good design for the type of data you are trying to describe and the application I think you are trying to build. That opinion is qualified because I do not know all of the details to your particular project so I cannot say for certain that this design is absolutely better than all others. However, based on the information you shared with the list, I think it will be a good fit for your needs.
Shawn Green Database Administrator Unimin Corporation - Spruce Pine Test USER <[EMAIL PROTECTED]> wrote on 12/19/2005 01:28:32 PM: > Thanks ALOT! > So you would say that this is good database design and that this method and > query is the best way to store and retrive product specifications? > > > Quoting [EMAIL PROTECTED]: > > > Is this good enough? > > > > SELECT prodid, count(1) matches > > FROM PRODUCT_SPECS > > WHERE (where SPEC_ID=1 and VALUE=YES) > > OR(and SPEC_ID=5 and VALUE>=28) > > GROUP BY prodid > > HAVING matches=2; > > > > This query form is flexible enough so that if, for instance, you just > > wanted to rank products based on how well they match a set of criteria, > > you could leave out the HAVING clause and replace it with ORDER BY > > matches > > DESC. > > > > If you only cared about matching a 3 of 4 query terms, your WHERE clause > > > > would list all 4 terms but your HAVING clause would only check for 3 of > > them. > > > > Normally, these results would be cached into a temporary table and > > re-used > > in other places (for speed) but it is possible to join in several other > > tables if you need them and still get decent results from a single > > statement. > > > > IMHO, Not only is this database design a flexible storage system but the > > > > ability to easily poll for partial matches and easily determine gross > > matching rankings makes it useful for many applications. > > > > Shawn Green > > Database Administrator > > Unimin Corporation - Spruce Pine > > > > Test USER <[EMAIL PROTECTED]> wrote on 12/19/2005 08:28:36 AM: > > > > > Great, :) > > > But do you know how to write a good select query using this design? > > > For example if i want to select all TV with widescreen and inch > > > greater than 28? > > > > > > select * from PRODUCT_SPECS (where SPEC_ID=1 and VALUE=YES) > > > (and SPEC_ID=5 and VALUE>=28) > > > > > > this doesnt feel right... > > > > > > > > > > > > From: SGreen at unimin dot com > > > Date: December 10 2005 3:29am > > > Subject: Re: CAST as S=?ISO-8859-1?Q?IGNED,_bad_id=E9a??= > > > > > > --=_alternative 000D3711852570D3_= > > > Content-Type: text/plain; charset="US-ASCII" > > > > > > MUCH BETTER!! Sorry I doubted you. However you have to remember that > > > unless you declare a second numeric column in your PRODUCT_SPECS table > > > > > then everything will be treated as strings. Sorting will be as > > strings, > > > comparisons will be as strings, and any attempt to use them as numbers > > > > > while they are strings will invalidate any indexes. > > > > > > I would suggest a second DECIMAL column on your PRODUCT_SPECS table or > > > > be > > > prepared for performance hits whenever you need numeric ordering. If > > you > > > compare them alphabetically, "8" comes after "1" so "8" is greater > > than > > > "10", "100", "1000", "20", "30", or any other "word" that starts with > > a > > > "letter" smaller than "8". > > > > > > Shawn Green > > > Database Administrator > > > Unimin Corporation - Spruce Pine > > > > > > Test USER <[EMAIL PROTECTED]> wrote on 12/09/2005 05:47:41 PM: > > > > > > > Oh, is it really such a bad design? Here is some more. > > > > What is substring matches, and why do i need them? > > > > > > > > > > > > TBL_PRODUCTS > > > > ID PRODUCTNAME > > > > 1 SAMSUNG TV > > > > 2 PHILIPS DVD-PLAYER > > > > 3 PHILIPS TV > > > > 4 MAXTOR DMAX > > > > 5 LaCie HARDDIVE > > > > > > > > --------------------------------------------- > > > > > > > > TBL_SPECS > > > > ID DETAIL > > > > 1 Widescreen > > > > 2 VCD > > > > 3 DiVX > > > > 4 Capacity > > > > 5 Inch > > > > > > > > > > > > --------------------------------------------- > > > > > > > > PRODUCT_SPECS > > > > PRODID SPECID VALUE > > > > 1 1 YES > > > > 1 5 32 > > > > 2 2 NO > > > > 2 3 3.11 > > > > 3 1 NO > > > > 3 5 28 > > > > 4 4 80 > > > > 5 4 120 > > > > > > > > ----------------------------------------- > > > > > > > > Thanks again for your help! > > > > > > > > Quoting [EMAIL PROTECTED]: > > > > > > > > > This sounds like a simple case of bad design. > > > > > > > > > > You need to be able to locate specific values for various product > > > > > descriptions but they are all mangled together into just one > > field. > > > You > > > > > end up trying to do substring matches and all hell breaks loose > > and > > > > > performance hits the skids. > > > > > > > > > > My suggestion is to somehow re-process your "value" column into > > > separate > > > > > > > > > > specific columns or child tables, one for each distinct value held > > > > in > > > > > the > > > > > "value" field. I can identify the potential values of `hdd_size`, > > > > > `monitor_size`, > > `monitor_resolution`,`hw_port`,`cd_supp_format`.... > > > > > > > > > > Your data is unmanageable in its present format and you need to > > scrub > > > > > and > > > > > massage it into shape before what you have will be marginally > > useful. > > > > > > > > > > Shawn Green > > > > > Database Administrator > > > > > Unimin Corporation - Spruce Pine > > > > > > > > > > > > > > > Test USER <[EMAIL PROTECTED]> wrote on 12/09/2005 04:56:21 PM: > > > > > > > > > > > Hi again :) > > > > > > > > > > > > The table contains a column named value and is in the format > > > > > varchar(255). > > > > > > This column contains specifications for different computer > > products. > > > > > > There is also a id column so i know which product it belongs to. > > > > > > > > > > > > value(varchar) > > > > > > 80 > > > > > > 17" > > > > > > 1024x768 > > > > > > USB > > > > > > DiVX > > > > > > > > > > > > For example, the first value 80 tells me with som joins that the > > > > > > > > product maxtor > > > > > > diamondmax has 80Gb capacity. And that a Philips DVD-player > > > > > supportsDiVX > > > > > for > > > > > > the last value in this example. > > > > > > > > > > > > Now i want to select all harddrvies with a capacity greater or > > equal > > > > > to > > > > > 80. > > > > > > Doing a "select value from tbl where value >=80 order by value > > > > > DESC"will > > > > > give > > > > > > som unexpected results. > > > > > > > > > > > > If you have 80, 120, 250 in the database the result will be: > > > > > > 80 > > > > > > 250 > > > > > > 120 > > > > > > > > > > > > I don't really know how to solve this other than to use > > CAST(value > > > > > as > > > > > SIGNED). > > > > > > Maybe i could rebuild the database but i don't know how a good > > > > > databasedesign > > > > > > for this would look like :) > > > > > > > > > > > > Thanks for you help! > > > > > > > > > > > > Quoting [EMAIL PROTECTED]: > > > > > > > > > > > > > I misunderstood, I thought you were looking for a way of > > > converting > > > > > your > > > > > > > > > > > > > > numbers-as-strings into a native numeric format. > > > > > > > > > > > > > > Please describe you situation better: What language are you > > using > > > to > > > > > > > build > > > > > > > your application. Are you composing the SQL statement > > client-side > > > or > > > > > > > > > > > > server-side? What kind of SQL statement are you trying to > > execute? > > > > > > > > > > > > > > Your table structures (the output of SHOW CREATE TABLE ... > > works > > > > > very > > > > > > > > > > > > well) and some sample data would also help. > > > > > > > > > > > > > > Sorry for the confusion! > > > > > > > > > > > > > > Shawn Green > > > > > > > Database Administrator > > > > > > > Unimin Corporation - Spruce Pine > > > > > > > > > > > > > > > > > > > > > Test USER <[EMAIL PROTECTED]> wrote on 12/09/2005 04:28:44 PM: > > > > > > > > > > > > > > > Hello, thanks for your help! > > > > > > > > I dont really get it :) > > > > > > > > > > > > > > > > You suggestion is to have a seperate column with the name > > > > > numericvalue > > > > > > > > > > > > > > and > > > > > > > > insert userinput into that and add a zero, right? > > > > > > > > > > > > > > > > Could you explain more, why when how will this help me :) > > > > > > > > > > > > > > > > Quoting [EMAIL PROTECTED]: > > > > > > > > > > > > > > > > > Assuming that your text data is in the column `userinput` > > and > > > > > you > > > > > > > want > > > > > > > > > the > > > > > > > > > integer values to be in the column `numericvalue`, this > > > > > statement > > > > > > > will > > > > > > > > > > > > > > > > populate the `numericvalue` column all at once: > > > > > > > > > > > > > > > > > > UPDATE `odd_data_table` SET `numericvalue` = `userinput` + > > > > 0; > > > > > > > > > > > > > > > > > > You are better off checking for type-correctness before > > you > > > > > enter > > > > > > > data > > > > > > > > > > > > > > > > into the database than you are trying to correct it after > > the > > > > > input. > > > > > > > > > > > > > > > > However, I have had to do just this kind of conversion on > > many > > > > > > > > > > occasions > > > > > > > > > > > > > > > > > > (old data, bad batch inputs, text file bulk loads, etc.) > > so > > I > > > > > > > > know > > > > > > > > > techniques like this still have their place. > > > > > > > > > > > > > > > > > > Shawn Green > > > > > > > > > Database Administrator > > > > > > > > > Unimin Corporation - Spruce Pine > > > > > > > > > > > > > > > > > > Test USER <[EMAIL PROTECTED]> wrote on 12/09/2005 03:30:17 > > PM: > > > > > > > > > > > > > > > > > > > in an application i have written there is the need to do > > a > > > > > > > search > > > > > > > from > > > > > > > > > > > > > > > > > > mysql > > > > > > > > > > using numbers that are stored in a varchar column. it is > > > > not > > > > > > > > > > > > > possible to store > > > > > > > > > > only the results with numbers in a seperate column. > > > > > > > > > > so i was looking at CAST(), is this a big performance > > loss? > > > is > > > > > > > > > theresome > > > > > > > > > way > > > > > > > > > > of benchmarking different queries easy? > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ------------------------------------------------- > > > > > > FREE E-MAIL IN 1 MINUTE! > > > > > > - [EMAIL PROTECTED] - http://www.pc.nu > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ------------------------------------------------- > > > > FREE E-MAIL IN 1 MINUTE! > > > > - [EMAIL PROTECTED] - http://www.pc.nu > > > > > > --=_alternative 000D3711852570D3_=-- > > > > > > > > > > > > ------------------------------------------------- > > > FREE E-MAIL IN 1 MINUTE! > > > - [EMAIL PROTECTED] - http://www.pc.nu > > > > > > > > ------------------------------------------------- > FREE E-MAIL IN 1 MINUTE! > - [EMAIL PROTECTED] - http://www.pc.nu
