Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Is this acceptable?

 
Ruben Matt
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi. I have an AutoCompleteText connecting to my db. The problem was that it was giving me many duplicate values so i tried to show me only one time:


This is working, at least every time i chose one and insert it and when i quit the app and check there's only one. My question is if it's viable to do this way or there's a way that's more, i don't know how to put it, right??
 
Steve Luke
Bartender
Posts: 4181
22
IntelliJ IDE Java Python
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It is hard to say if it is a good approach or not. It depends on what the things are getting, why they are duplicated, and how they will be used.

For example, if each 'equipment' row is supposed to be unique, then you have to ask why are there duplicates? Are you inserting when you should be updating? Is your check to see if a piece of equipment is present not comparing correctly? etc... You need to identify if the 'problem' is present in the database/caused by bad insertion code and if it, this is just hiding the problem which might bite you later on (with inflating storage sizes or inconsistent/partial data storage).

Or it could be that the equipment field is not meant to be unique, and multiple values could represent different articles. For example, 'drill' could be for a Craftsman long handled manual drill, a Black & Decker electric drill, or Toyo high powered machinist's drill. So it could be that you are displaying the wrong field, instead of displaying 'drill' 3 times, you should display something more descriptive so you can identify each item it represents.

Or it could depend on the usage. The same piece of equipment is listed multiple for the reason above, but you want to do a generic search for drill, and not something specific like the more detailed particular drill. In that case you are doing it correctly by filtering so each 'generic' term only shows up once (although, in that case, I might think about creating a new table with the type and an id, then have each row in the garage table use the id for the equipment rather than name rather than have the equipment repeated many times. This could save time in the auto complete query which would have fewer rows to search, and could have other benefits like keeping things more consistent across tables/views or making searches for all items of a particular type faster...)
 
Ruben Matt
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The situation is every time i want to insert a project in the database i have a field name equipment and the AutoCompleteText goes to the field equipment of the database and retrieves all the values, if it retrieves everything of course i would have some values duplicated and so i tried to find a way to each equipment only appear one time. The value is not unique. Have i made myself clearer??
 
Steve Luke
Bartender
Posts: 4181
22
IntelliJ IDE Java Python
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Then it sounds like the third scenario I had before: it depends on usage. and as described, I might create a separate table just to hold the equipment types, for example in a table called EquipmentType which has an ID and the text name of the piece of equipment.

Your query for the entries would then have a smaller list to search through, and theoretically would be faster when there are more items. It would be SELECT equipment FROM EquipmentType. When the form is filled, you would do two insert statements, one into the EquipmentTypes table and one into the Garage:


Then, when building the garage table for display you join the Garage and EquipmentTypes tables:


Here is the SQL I used for testing. Note, becomes much more valuable when you have lots of items in the Garage and there is a lot of overlap in the Equipment field:

1. Create an EquipmentTypes table and add some values to it:
output:
1 Drill
2 Hammer


2. Test an in-app insert, including a new equipment type and one that already exists:

output:
1 Drill
2 Hammer
3 Screwdriver


3. Create the Garage table, and fill it with some values:

output:
1 1 B&D electric carpenters drill
2 3 Phillips head #3.5


4. Join the Garage table and the EquipmentTypes table so you get a better description of the item in the garage:

output:
1 Drill       B&D electric carpenters drill
2 Screwdriver Phillips head #3.5


5. A user fills the form with the equipment 'Hammer' and the item name 'Steel ball peen hammer'.

output
1 Drill       B&D electric carpenters drill
2 Screwdriver Phillips head #3.5
3 Hammer      Steel ball peen hammer


6. Add more overlapping equipment, and order display the type:

output
1 Drill       B&D electric carpenters drill
3 Hammer      Steel ball peen hammer
6 Hammer      6" Claw Hammer
2 Screwdriver Phillips head #3.5
4 Screwdriver Phillips head #2
5 Screwdriver Phillips head #4
7 Screwdriver Flat head #3
8 Screwdriver Flat head #1

 
Ruben Matt
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi. Thanks for your help, i'm going to try this.

Thank you.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic