I'm currently designing a DB for a project I'm working on, till I hit a crossroad. So I'm seeking the correct approach.

My DB contains different tables for different categories. Hence these categories tables, contain different products. My current setback during the design process arose when I decided to include a search form for the project.

Now I need to be directed, which one is a better practice? Creating another table with all the products in it which would be used to match the user's query? That is, I'd have the different tables for the different products then still have this table which would contain only the product names, and would be linked to the appropriate category table. This approach seems redundant in a way, I know.

My next approach is to completely eliminate the different category approach model and just have a giant table housing all the products and their details.

Thirdly, use the different category approach but loop through all the tables when a search query is entered and return the appropriate result.

Fourthly?? Abeg, in case all the models are wrong, which model would be the best approach?? I dey beg, so I fit continue. Thanks!!

NB: Using MySQL.

DharkPoet:
I'm currently designing a DB for a project I'm working on, till I hit a crossroad. So I'm seeking the correct approach.

My DB contains different tables for different categories. Hence these categories tables, contain different products. My current setback during the design process arose when I decided to include a search form for the project.

Now I need to be directed, which one is a better practice? Creating another table with all the products in it which would be used to match the user's query? That is, I'd have the different tables for the different products then still have this table which would contain only the product names, and would be linked to the appropriate category table. This approach seems redundant in a way, I know.

My next approach is to completely eliminate the different category approach model and just have a giant table housing all the products and their details.

Thirdly, use the different category approach but loop through all the tables when a search query is entered and return the appropriate result.

Fourthly?? Abeg, in case all the models are wrong, which model would be the best approach?? I dey beg, so I fit continue. Thanks!!

NB: Using MySQL.

First of all, the title is misleading... I thought you were having issues with SQL queries, it is related to database design.

The second approach seems to be okay but will be messy and costly as you expand if you don't scale, you can use the approach tho. (Not all sites will be as big as Fb, twitter or even Konga).

The best method (I stand to be corrected) would be to add an extra column for the product category then you can easily search for products WHERE product_category = user_input instead of having another table to house the categories. Remember your database has to be flexible and not hard-coded.

That third approach will possible dig the website's grave, funny to even think about it.. Third approach = Slower results (except you add more tweaks here and there)

Try using http://www.dbdesigner.net to visualize your concepts first or check for a previously existing design you can implement.

DanielTheGeek:


First of all, the title is misleading... I thought you were having issues with SQL queries, it is related to database design.

The second approach seems to be okay but will be messy and costly as you expand if you don't scale, you can use the approach tho. (Not all sites will be as big as Fb, twitter or even Konga).

The best method (I stand to be corrected) would be to add an extra column for the product category then you can easily search for products WHERE product_category = user_input instead of having another table to house the categories. Remember your database has to be flexible and not hard-coded.

That third approach will possible dig the website's grave, funny to even think about it.. Third approach = Slower results (except you add more tweaks here and there)

Try using http://www.dbdesigner.net to visualize your concepts first or check for a previously existing design you can implement.

Exactly, and if you have attributes associated with the category entity, you can create a seperate table for categories and then add a refrence key t products table refrencing the categories table

DanielTheGeek:


First of all, the title is misleading... I thought you were having issues with SQL queries, it is related to database design.

The second approach seems to be okay but will be messy and costly as you expand if you don't scale, you can use the approach tho. (Not all sites will be as big as Fb, twitter or even Konga).

The best method (I stand to be corrected) would be to add an extra column for the product category then you can easily search for products WHERE product_category = user_input instead of having another table to house the categories. Remember your database has to be flexible and not hard-coded.

That third approach will possible dig the website's grave, funny to even think about it.. Third approach = Slower results (except you add more tweaks here and there)

Try using http://www.dbdesigner.net to visualize your concepts first or check for a previously existing design you can implement.

Thanks a lot, first off, I apologize for the misconception as regards to the title of my post.

My thoughts match yours almost exactly, that is in terms of using the 2nd and 3rd approaches, I felt there would be efficiency issues if the table grew for the 2nd approach, and for the 3rd approach, it's a 'website grave digger', I ruled it out immediately I thought about it, just decided to post it as a side-thought.

So the issue is this, the categories table already has the product_column. But the issue is this, I'm not trying to search for 'WHERE product_category=user_input' rather I'm trying to do this 'WHERE product=user_input'.

Thanks, I'd use the link you posted. I appreciate.

Optimally, you'll need to normalize your database. You need a single category table and a single product table. I'm assuming a category can have several products, and a product can be in one category.
Category Table: category_id, category_name, category_description
Product Table: product_id, category_id, product_name, product_description

With this setup, you can easily search for a product: where product_name = @product_name

You can return a product and it's category by joining both tables or even better you can create a view. Let me know if you need more help.

DharkPoet:

Thanks a lot, first off, I apologize for the misconception as regards to the title of my post.

My thoughts match yours almost exactly, that is in terms of using the 2nd and 3rd approaches, I felt there would be efficiency issues if the table grew for the 2nd approach, and for the 3rd approach, it's a 'website grave digger', I ruled it out immediately I thought about it, just decided to post it as a side-thought.

So the issue is this, the categories table already has the product_column. But the issue is this, I'm not trying to search for 'WHERE product_category=user_input' rather I'm trying to do this 'WHERE product=user_input'.

Thanks, I'd use the link you posted. I appreciate.

You can't run away/stop your tables from growing big, this is where the type of database storage matters. This is also why people dump SQL to Redis, Hadoop and co.
But again, not all sites will be as big as facebook or twitter, just follow good conventions and use cron-jobs to delete dormant rows, things should be okay for now.
For the issue of querying, follow what @sam said.

samm:
Optimally, you'll need to normalize your database. You need a single category table and a single product table. I'm assuming a category can have several products, and a product can be in one category.
Category Table: category_id, category_name, category_description
Product Table: product_id, category_id, product_name, product_description

With this setup, you can easily search for a product: where product_name = @product_name

You can return a product and it's category by joining both tables or even better you can create a view. Let me know if you need more help.

Thanks a lot. This pointed me in the right direction. I appreciate.

DanielTheGeek:


You can't run away/stop your tables from growing big, this is where the type of database storage matters. This is also why people dump SQL to Redis, Hadoop and co.
But again, not all sites will be as big as facebook or twitter, just follow good conventions and use cron-jobs to delete dormant rows, things should be okay for now.
For the issue of querying, follow what @sam said.

Thanks a lot Daniel. You've really helped me. Talking about the efficiency of SQL, I've read about people dumping it for alternatives like Redis et al, what would your advice in terms of using SQL for a classified ads site.

DharkPoet:

Thanks a lot Daniel. You've really helped me. Talking about the efficiency of SQL, I've read about people dumping it for alternatives like Redis et al, what would your advice in terms of using SQL for a classified ads site.

Go ahead, no problem. By the time you get to SQL's limit, you must have made enough money to hire developers to re-implement the db storage that's more scalable.

DanielTheGeek:


Go ahead, no problem. By the time you get to SQL's limit, you must have made enough money to hire developers to re-implement the db storage that's more scalable.

Ahmeen!!! Thanks a lot brother. I appreciate your help.