Structuring / Searching an Ecommerce Database

Databases
1

Hi all

I’m in the process of creating a custom e-commerce system. Now I have experience with working in PHP and MySQL, and in specifically with some (relatively basic) ecommerce websites, but I’d just like to bounce some ideas off you to see how you think certain things should be organised.

At the end of the day, I’m trying to make sure I plan this pretty well before I begin - because I know (from experience unfortunately) that messing with databases once the system is up and running is not easy, and not fun.

My concerns at the moment are mainly regarding the structure of my database and in particular how that will impact searching and storing products.

These are my main concerns at the moment:

Table organisation. Obviously this system will need pretty detailed separation in order to have features such as product options. So, for example, I think (from looking at other systems) I’d need a product table (for a car), then an option table (colour), then an option_value table (blue), then a product_option table (car has colour) and a product_option_value table (car’s colour is blue). Is this the best way? It’s long-winded yes, but it gives the most freedom and expressiveness, doesn’t it?

My search feature should really take into account all (or some of?) these options. Many ecommerce systems I see don’t, but if you take eBuyer.com for example, its search feature is great because it lets you filter by properties and options. If I have tables organised like I suggested above, how could I implement some sort of filter-based search? Ideally, it would need to look at the common options available for my search terms, and allow the user to filter those…

Talking of searching, I’ve struggled to come up with good SQL queries for searching. For example, if I use basic %LIKE%, and search for “laptops” I won’t get a product with ‘laptop’ in the name (without the ‘s’) - and I need to search both categories and products, and surely products in a matching category would be given preference? I’m trying to figure out how I could structure my searches to allow for these little niggles. Essentially, I want my search to be pretty comprehensive.

Indexing… Should I index the descriptions, or name fields, of my products? Would it provide any benefit? Also searching through indexing allows for more flexibility than, for example, %LIKE% when searching. Would it provide me with a significant benefit? And are there huge downsides? I found a great article on full-text indexing a while ago, I will have a look for them so you can see what you think?

…

I know it seems like I’m asking for a lot from you, but to clarify: I don’t just want you to necessarily tell me what to do, I want to bounce some ideas around from people who have experience with these kinds of decisions. For example, how would you organise such a system? Or have I missed something blindingly obvious? It’s good just to get some outside perspective.

Also, I’m guessing this is in the right forum, but please could someone move it if not?

Thanks in advance, looking forward to hearing your ideas and discussing this.
Jake

2

Ah ok, thanks for that… the e-commerce system I’m building is intended to be used to host multiple shops. This is where my problem comes in, because each shop could have different option requirements, and could be of different sizes - but for simplicity’s sake, let’s say a few hundred shops each with a few hundred products.

Also, sorry for being so slow to reply, I’ve been away from a PC recently unfortunately.

Also, what do you (or anyone) think of this link? I found it to be quite an interested read, but obviously raised a lot more questions about search result relevance.

http://r937.com/keyword_relevance.html

Thanks
Jake

3

that depends

for cars, if you’re one of the auto makers, it might make sense, but if you’re Joe’s Used Cars around da corner, it’s overkill

you neglected to mention what kind of products you’re selling, or how big your store is (number of products)