Click here to Skip to main content

Welcome to the Lounge

   

For lazing about and discussing anything in a software developer's life that takes your fancy.
The Lounge is rated PG. If you're about to post something you wouldn't want your kid sister to read then don't post it. No flame wars, no abusive conduct and please don't post ads.

Technical discussions are welcome, but if you need specific help please use the programming forums.


 
GeneralRe: Swatting a fly with a nuke PinmemberKyudos15-Jan-13 10:04 
GeneralRe: Swatting a fly with a nuke PinmemberBassam Abdul-Baki15-Jan-13 14:56 
GeneralRe: Swatting a fly with a nuke PinmemberKyudos15-Jan-13 16:13 
GeneralRe: Swatting a fly with a nuke PinmemberBassam Abdul-Baki16-Jan-13 0:37 
GeneralWell, I Give Up (on SQL) PinprotectorAspDotNetDev14-Jan-13 15:09 
GeneralRe: Well, I Give Up (on SQL) PinprotectorMarc Clifton14-Jan-13 15:33 
GeneralRe: Well, I Give Up (on SQL) PinadminChris Maunder14-Jan-13 15:35 
GeneralRe: Well, I Give Up (on SQL) PinprotectorAspDotNetDev14-Jan-13 16:14 
I don't want to give you anything solid (wouldn't want to risk you solving my problem, I might get reported!). However, I'll give you the gist (also, I'll be speaking in generalities to make it more confusing).
 
The user can type in some characters, and they get sent to a stored procedure to see if those characters occur in any of the data (the data will then be returned to the page so the textbox can show an auto-complete list of 10 or so items). The code is smart enough that it can massage the input in various ways to match it up with the data (e.g., remove special characters, ignore casing, ignore whitespace, manipulate "ab" so that it looks for "cd" instead).
 
The data resides in various tables. About 4. One of the tables actually just provides aliases for the values in another table (e.g., "ducks" are also known as "mallards"). And the data returned for auto-completion is only from 2 of the four tables (the alias table and another table are linked back to the other 2 tables to find the return data). There is further data in other tables that indicate attributes of the main 4 tables, but they are only connected via the 2 auto-complete tables (so some tables require multiple joins to get to the attributes). There are roughly 200 attributes per row in the main 4 tables. The user can select among those attributes to filter by (e.g., they can select 40 of the 200 attributes so only results with those 40 attributes will be returned).
 
Also, I kind of lied when I said the data resided in 4 tables. There are tables which indicate the history of the changes to the data in the other tables. So those history tables are also included. And that history is actually of the changes to the attributes, so it magnifies the size of the data considerably. This on top of the main 4 tables, each of which contain tens of thousands to hundreds of thousands of records.
 
To give a concrete, but fictitious example, imagine a store that sells belts. They have thousands of types of belts, and each type of belt has other factors that can be varied (size, buckle type, etc.), which create a few hundred thousand unique belt combinations. Now, there is one attribute in particular that customers like to choose from... say, the color. So, there are 200 colors to choose from. The belts are stored in database tables according to various aspects. For one, belt name. Also, they are stored by an internal belt identifier as well as a SKU shown on the belt. They are also stored by the ID of the documents used to describe the various uses for the belt. Whenever one belt color goes out of style, a record is tossed in another table. Whenever a new belt color goes into style, that is tossed in that same table. Sometimes colors go out of style, sometimes colors come into style, and sometimes color styles are replaced with other color styles. Sometimes an out of style color comes back into style.
 
Now, I'd like to provide an auto-complete box for belts. The user can type in the belt name, the internal belt ID, the public belt SKU, or the belt document ID. The auto-complete will only return belt name and public belt SKU, but the user can type in any of the 4 primary aspects (though, if they type in internal belt ID or belt document ID, the corresponding public belt SKU will be shown). Colors in the database happen to be related to public belt SKU, and the user may enter any of the colors they are interested in (say, all shades of red, 3 shades of purple, and a few greens). The auto-complete should only show belts that are available or were ever available in the belt colors specified by the user. Results must be returned in less than a second, and you have a day to complete this. Oh, and you are on SQL Server 2005, so no table-valued parameters.
 
Good luck. Smile | :)
 
P.S. If my manager is really interested in this feature, I may give it another go. We'll see.

GeneralRe: Well, I Give Up (on SQL) PinmemberPIEBALDconsult14-Jan-13 16:54 
GeneralRe: Well, I Give Up (on SQL) PinprotectorAspDotNetDev14-Jan-13 17:23 
GeneralRe: Well, I Give Up (on SQL) PinadminChris Maunder14-Jan-13 18:14 
GeneralRe: Well, I Give Up (on SQL) PinprotectorAspDotNetDev14-Jan-13 18:55 
GeneralRe: Well, I Give Up (on SQL) PinmemberNagy Vilmos15-Jan-13 0:10 
GeneralRe: Well, I Give Up (on SQL) PinmemberPIEBALDconsult14-Jan-13 15:52 
GeneralRe: Well, I Give Up (on SQL) PinmemberRutvik Dave14-Jan-13 18:32 
GeneralRe: Well, I Give Up (on SQL) PinmemberJim Meadors14-Jan-13 20:05 
GeneralRe: Well, I Give Up (on SQL) Pinmemberdigimanus14-Jan-13 21:58 
GeneralRe: Well, I Give Up (on SQL) PinprotectorAspDotNetDev15-Jan-13 3:40 
GeneralRe: Well, I Give Up (on SQL) Pinmemberdigimanus15-Jan-13 3:43 
GeneralDell Buyout PinmemberKevin Marois14-Jan-13 11:16 
GeneralRe: Dell Buyout PinmentorDaveAuld14-Jan-13 11:29 
GeneralRe: Dell Buyout Pinmember_Maxxx_14-Jan-13 12:26 
GeneralRe: Dell Buyout PinmentorDaveAuld14-Jan-13 12:31 
GeneralRe: Dell Buyout Pinmember_Maxxx_14-Jan-13 12:47 
NewsHMV Next? [modified] PinmentorDaveAuld14-Jan-13 9:59 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.


Advertise | Privacy | Mobile
Web03 | 2.8.140916.1 | Last Updated 17 Sep 2014
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid