Click here to Skip to main content
13,355,292 members (58,638 online)

Welcome to the Lounge

   

For discussing anything related to a software developer's life. Technical discussions are encouraged, but click here to ask your programming questions.

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, no programming questions and please don't post ads.
 
GeneralRe: Swatting a fly with a nuke Pin
Kyudos15-Jan-13 11:04
memberKyudos15-Jan-13 11:04 
GeneralRe: Swatting a fly with a nuke Pin
Bassam Abdul-Baki15-Jan-13 15:56
memberBassam Abdul-Baki15-Jan-13 15:56 
GeneralRe: Swatting a fly with a nuke Pin
Kyudos15-Jan-13 17:13
memberKyudos15-Jan-13 17:13 
GeneralRe: Swatting a fly with a nuke Pin
Bassam Abdul-Baki16-Jan-13 1:37
memberBassam Abdul-Baki16-Jan-13 1:37 
GeneralWell, I Give Up (on SQL) Pin
AspDotNetDev14-Jan-13 16:09
protectorAspDotNetDev14-Jan-13 16:09 
GeneralRe: Well, I Give Up (on SQL) Pin
Marc Clifton14-Jan-13 16:33
protectorMarc Clifton14-Jan-13 16:33 
GeneralRe: Well, I Give Up (on SQL) Pin
Chris Maunder14-Jan-13 16:35
adminChris Maunder14-Jan-13 16:35 
GeneralRe: Well, I Give Up (on SQL) Pin
AspDotNetDev14-Jan-13 17:14
protectorAspDotNetDev14-Jan-13 17: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) Pin
PIEBALDconsult14-Jan-13 17:54
memberPIEBALDconsult14-Jan-13 17:54 
GeneralRe: Well, I Give Up (on SQL) Pin
AspDotNetDev14-Jan-13 18:23
protectorAspDotNetDev14-Jan-13 18:23 
GeneralRe: Well, I Give Up (on SQL) Pin
Chris Maunder14-Jan-13 19:14
adminChris Maunder14-Jan-13 19:14 
GeneralRe: Well, I Give Up (on SQL) Pin
AspDotNetDev14-Jan-13 19:55
protectorAspDotNetDev14-Jan-13 19:55 
GeneralRe: Well, I Give Up (on SQL) Pin
Nagy Vilmos15-Jan-13 1:10
memberNagy Vilmos15-Jan-13 1:10 
GeneralRe: Well, I Give Up (on SQL) Pin
PIEBALDconsult14-Jan-13 16:52
memberPIEBALDconsult14-Jan-13 16:52 
GeneralRe: Well, I Give Up (on SQL) Pin
Rutvik Dave14-Jan-13 19:32
memberRutvik Dave14-Jan-13 19:32 
GeneralRe: Well, I Give Up (on SQL) Pin
Jim Meadors14-Jan-13 21:05
memberJim Meadors14-Jan-13 21:05 
GeneralRe: Well, I Give Up (on SQL) Pin
digimanus14-Jan-13 22:58
memberdigimanus14-Jan-13 22:58 
GeneralRe: Well, I Give Up (on SQL) Pin
AspDotNetDev15-Jan-13 4:40
protectorAspDotNetDev15-Jan-13 4:40 
GeneralRe: Well, I Give Up (on SQL) Pin
digimanus15-Jan-13 4:43
memberdigimanus15-Jan-13 4:43 
GeneralDell Buyout Pin
Kevin Marois14-Jan-13 12:16
memberKevin Marois14-Jan-13 12:16 
GeneralRe: Dell Buyout Pin
DaveAuld14-Jan-13 12:29
mentorDaveAuld14-Jan-13 12:29 
GeneralRe: Dell Buyout Pin
_Maxxx_14-Jan-13 13:26
member_Maxxx_14-Jan-13 13:26 
GeneralRe: Dell Buyout Pin
DaveAuld14-Jan-13 13:31
mentorDaveAuld14-Jan-13 13:31 
GeneralRe: Dell Buyout Pin
_Maxxx_14-Jan-13 13:47
member_Maxxx_14-Jan-13 13:47 
NewsHMV Next? Pin
DaveAuld14-Jan-13 10:59
mentorDaveAuld14-Jan-13 10:59 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    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 |
Web04 | 2.8.180111.1 | Last Updated 22 Jan 2018
Copyright © CodeProject, 1999-2018
All Rights Reserved. Terms of Service
Layout: fixed | fluid