Need to make a search feature

[cherishedmoments]

Ok, I own a shop that sells engraved gifts. I've always helped my customers come up with ideas for engravings and they tell me quite frequently that I'm "creative" when it comes to coming up with fresh ideas. For example, I engraved a glass plate for a gentlemen one time that was going to ask his girlfriend to marry him. I suggested that he engrave the bottom with "Marry Me" (or some sort of surprise message that she would find) on the bottom and serve her dinner on the plate. Half-way through the meal she realized there was something engraved on her plate and she started pushing her food aside to try to read it. He loved the idea and he even shared the "after" story with me (which was so great)!

So, I was thinking....I'd love to be able to create a search feature on my store where the customer enters in an occasion and a gender and they will get a list of (my) ideas for engravings. I've run into a couple of snags. First of all....I'm not sure how to set up my tables in my database. I mean, I know how to physically do it....I'm just not sure what to put in the fields. You see....it's not so simple to narrow down the results. Let me give you an example of what I mean:

 

In our shop, we sell a metal keychain. It is rectangular and fairly flat. It has an area for engraving. On the end of it is a flashlight.

 

Customer #1 wants a gift for her co-worker for Secretary's Day. She saw her co-worker fumbling in the dark with her keys one night and thought she could use our flashlight keychain. She asks me what she might engrave. I tell her that she could engrave, "You are the light of our office!" or something to that effect.

Customer #2 wants a gift for her niece on her birthday. The niece is turning 16 and is excited about getting her driver's license. She has no clue what to buy so I suggest a keychain (for her new key set!). I tell her about our keychains and she chooses this one. She decides to engrave, "Happy Sweet 16"

Customer #3 wants a gift for her boyfriend "just because". I ask her about any special songs or sayings that are special to them. She says "their song" is "You light up my life" so, of course, I suggest the flashlight keychain with the name of the song engraved on itI Since it's not a gift for a special occasion, he might think it's cute. (Yes, I know. I'm just dreaming! No guy thinks ANYTHING is "cute". Just work with me here for the purpose of this concept!)

 

Anyway....in case you haven't figured out my problem yet....all 3 people bought the same gift. Two of them bought for a female, one of them bought for a male. One of them bought a "romantic" gift and two did not. All 3 of them were buying for different occasions (Secretary's Day, Birthday, & "Just Because"). So I can't just put a product in ONE category nor is each item gonna have one suggestion for engravings. Almost every product we have could be for any occasion, any gender, etc. It's what they engrave that will be unique (usually). Although, sometimes, even that is the same! (i.e. "Happy Birthday") I might look for a gift for a male for Valentine's Day. One might be my boyfriend and one might be my Dad (we exchange Valentine's Gifts in my family). I might buy them something different (or the same) but the engraving would definitely be different.

 

So, first of all....how would you set up the tables? And, two....how would you set up the code to narrow it down? The ultimate goal is for someone to narrow down the list according to who they're buying for and the occasion and I want my database to output a list that gives them.....Column 1: Gift item suggestions and.....Column 2: One or more (probably more) ideas of things they can engrave onto each item.

 

I hope I made sense! Sorry it was so long! Do you think this is possible? Where should I start? I just can't wrap my head around this!

[MrPhil]

Whoa, slow down there. You're getting way ahead of yourself. OK, so you have a product, e.g., the flashlight keychain. It's listed only under one product category (why exactly do you want to list a product under multiple categories?). When placing the order, the customer includes "customization" information telling you what text to engrave. Your problem is that they often don't have any idea just what to engrave there (and thus, a sale is lost). You have a "suggestion file" of occasions and suitable engravings, and you want to make it customer-searchable for just the perfect text? The engravings for, say, a flashlight keychain would be separate from engravings for a dinner plate proposal (not much overlap)? If there's not much duplication, and the lists aren't terribly long, I would either include them with the product description text, or put an HTML link into the product description, going to a nicely formatted page showing what the product and sample engraving looks like and giving a long list of possible engravings (arranged in broad categories).

 

The problem with putting them into a database and searching by occasion and gender and relationship and whatever is that customers will probably miss a lot of great ideas if they don't see other entries (serendipity) or they don't enter quite the right keywords (setting up a good book index or a good search is a lot harder than it sounds). If your lists are huge, though, this might overwhelm customers, although you could have links to sections up at the top of the page to cut it down to manageable size (birthdays, job-related, friendship, etc., with gender and relationship subheadings). And of course, there will always be some sayings or occasions that are just too hard to categorize, or that a customer would never guess your keywords to find! In any case, why not invite them to contact you (via a contact form or email) with the specifics and ask for a recommendation? If you're such a whiz at it, you could probably select an appropriate engraving text and send it back for approval pretty quickly. I'm assuming that they don't take your free advice and then go to someone else to get it engraved at a lower price! That's always a hazard if you don't collect the money before they take your time.