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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
