relation schema for dealer locator mysql and php

Question

I have a project where there is a dealer locator which is supposed to locate the dealers in the area. The dealers supply multiple brands of different items(example: cement bricks etc.).

These items have subcategories and may also have a variable length sub categories. Under the sub categories are the brands. Brands are lowest level of that product , meaning there is no end product, all these put together is the product
These dealers have a location which is used to map them on google maps.
The locator must have filters where one can select the category and then a sub category and if exists a sub sub category . Also one can select the brand and quantity of the product.

I have tried different ways to create the database , but have failed. I am having difficulty in accessing products which have to be displayed after selecting each filter option.

My questions:

Should I create a table for every product , if so what will be the attribute?
How to handle the variable length sub categories ?
How can I access each product based on category or sub category or brand.

Sample schema:

table:categories(Parent_ID | Category_ID | Category_Name);
table:product (Dealer_ID| Brand_ID|Quantity); - table for each product
table:brands(brand_ID|Category_ID|Brand_Name);
table:Dealer(dealer_ID|lat|long|name ...etc.);
When the page loads all dealers are shown in the map based on location.
On selecting each option the page reloads and displays the dealers based on the selection.
With my current schema I am unable to access them based on selection.

How about some sample data to demonstrate what you're working with? I'm guessing you should not make a table per product, that's rarely wise, but without sample data we can't know. — Hart CO
your question is difficult to read in its current format as a Wall of Text. (1) table for every product - No. (2) variable length sub categories - use columns to show parent (next level up category/subcategory). (3) access each product - using subqueries and/or joins. What would make it easier is for you to map out some sample data and then find ways to you to condense/link similar and/or sub data. — Sean

jhansen jhansen · Accepted Answer · 2015-04-26T15:39:50

I would create a single products table, and create an entry for each unique product (not one for each categorical permutation). So Cement Brick, Shovel, Hammer, etc.

For the categories, I would create a category map table that would have the following fields: product_id(int), parent_category_id(int), child_category_id(int)

Both of those fields would map to a category table that would look something like: id(int), name(text), status_id(int), created_at(timestamp), updated_at(timestamp), deleted_at(timestamp)

This implementation would allow you to have as many sub-categories as you wish, and would allow for easy lookups since you'd just need to see if the child_category_id matched one in the requested filter.

As for the brand question, it really depends on how detailed you need to be concerning brand. If you don't need anything other than .. say a name, you could use First Normal Form and put the brand on the product entry itself (as a column on your products table). If you need some more details, and / or need to associate a single brand with multiple products.. you could use a basic xref.

relation schema for dealer locator mysql and php

1 Answers