[OT] Query design question
My database schema references a number of lookup tables. For example, DATA: id primary key category references lookup_category(key) upload_loc references lookup_upload_location(key) (etc) LOOKUP_CATEGORY: key primary key name description LOOKUP_UPLOAD_LOCATION: key primary key url description When doing a search, I create an ArrayList DATA beans to hold the search results. I want the results to display the long name rather than key for various lookup tables, etc. e.g., Acousto-Photonic Imaging rather than api. Is it a better design to design the query to handle this, e.g. SELECT a.id, b.name from data a, lookup_category b WHERE a.category = b.key; Even though an instance of the data bean doesn't now correspond directly to a DATA table entry? or to conduct separate queries. The first obviously seems faster. I'm sure this is handled by some design pattern.. but I'm not sure where to look. TIA, Becky - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [OT] Query design question
Not sure if this is what your looking for, but I have a similar problem where I have keys and long names for db lookup. When my app starts up, one of the first things that happens is that the first user in triggers a method that creates an xml file pulling all the keys in and their related long names and storing them in the context somewhere, so then the keys can be used for the db searches, and the xml file can be used to get the long names when they are required. Cheers Simon (];o) = Very poor attempt at a bald man!! - Original Message - From: Becky Norum [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, March 28, 2003 12:28 PM Subject: [OT] Query design question My database schema references a number of lookup tables. For example, DATA: id primary key category references lookup_category(key) upload_loc references lookup_upload_location(key) (etc) LOOKUP_CATEGORY: key primary key name description LOOKUP_UPLOAD_LOCATION: key primary key url description When doing a search, I create an ArrayList DATA beans to hold the search results. I want the results to display the long name rather than key for various lookup tables, etc. e.g., Acousto-Photonic Imaging rather than api. Is it a better design to design the query to handle this, e.g. SELECT a.id, b.name from data a, lookup_category b WHERE a.category = b.key; Even though an instance of the data bean doesn't now correspond directly to a DATA table entry? or to conduct separate queries. The first obviously seems faster. I'm sure this is handled by some design pattern.. but I'm not sure where to look. TIA, Becky - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [OT] Query design question
Simon, Thanks for the suggestion - that's a good idea with less OH. Also makes me think of just storing them in a application level Hashmap bean or something loaded at application startup, which is how I handle form collections.. Hmmm.. Becky Bald man looks more like a lady with a bouffant cuz of the parenthesis ;) On Fri, 2003-03-28 at 06:48, Simon Kelly wrote: Not sure if this is what your looking for, but I have a similar problem where I have keys and long names for db lookup. When my app starts up, one of the first things that happens is that the first user in triggers a method that creates an xml file pulling all the keys in and their related long names and storing them in the context somewhere, so then the keys can be used for the db searches, and the xml file can be used to get the long names when they are required. Cheers Simon (];o) = Very poor attempt at a bald man!! - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [OT] Query design question
Damn ... keyboard just don't offer enough options ... well to the coffee room for more of the columbian magic ... M a truely dark roast!! - Original Message - From: Becky Norum [EMAIL PROTECTED] To: Struts Users Mailing List [EMAIL PROTECTED] Sent: Friday, March 28, 2003 1:06 PM Subject: Re: [OT] Query design question Simon, Thanks for the suggestion - that's a good idea with less OH. Also makes me think of just storing them in a application level Hashmap bean or something loaded at application startup, which is how I handle form collections.. Hmmm.. Becky Bald man looks more like a lady with a bouffant cuz of the parenthesis ;) On Fri, 2003-03-28 at 06:48, Simon Kelly wrote: Not sure if this is what your looking for, but I have a similar problem where I have keys and long names for db lookup. When my app starts up, one of the first things that happens is that the first user in triggers a method that creates an xml file pulling all the keys in and their related long names and storing them in the context somewhere, so then the keys can be used for the db searches, and the xml file can be used to get the long names when they are required. Cheers Simon (];o) = Very poor attempt at a bald man!! - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]