MySQL Database-Table setup

Posted on 2014-02-22
Medium Priority
Last Modified: 2014-03-19
Hello experts.
I need your opinion how is the best way to setup my tables.
I want to store data of user shops.
I have two ways in my mind:
1.) shoptable with columns :
shop_id(primary)  user_id shopname .......

2.) categories table with columns:
category_id(primary) category_name

3.)regionstable table with:
region_id  subof region_name

4.)shop_categories table with:
shop_id   category_id

5.)shop_regions table with:
shop_id region_id

The second idea is to use instead 4 and 5 table another table:
shop_regions_categories with one type(char) column:
type(char) shop_id cat_region_id

Attached one example.
Any idea for the best solution or maybe another one?
Wich is the best way to catch data in a WHERE clause?
shop_id(primary)   user_id  Shop_name     shop_phonenumber  shop_address shop_postalcode  shop_point
2                  11       Myshopname     12345678         Mystreet     mypostalcode      mylatlng
category_id  category_name
1              firstcategory
2              secondcategory

region_id subof region_name
1         0    A_primary_region
2         1    A_sub_region_1
3         2    A_sub_region_2

shop_id category_id
2        1
2        2

shop_id region_id
2       1
2       3

OR use one table with one column type(char) 

type(char)   shop_id cat_region_id
CAT          2       1
CAT          2       2
REG          2       1
REG          2       3

Open in new window

Question by:Panos
  • 3
  • 2
LVL 16

Expert Comment

by:Kamal Khaleefa
ID: 39880285
well the design depend on your requirement and the data  that will provide.

for my usually if i have a table of users and a table of shops
i will create a third table that links both so i can link more than a shop with one user
also i will add a coloumn Called Status (boolean true or false)

so i can disable the relation once i need.

Author Comment

ID: 39880295
Hi king2002
I have a users table too. I did not include it here because the problem was to set up tables so that i can get faster results when i'm trying to find a shop with a query  dependet on the 1) location 2) the categories it belongs 3) and the regions it belongs.
LVL 16

Accepted Solution

Kamal Khaleefa earned 2000 total points
ID: 39880611
Then based on you requirement
Create a table that links user with a shop

And then a table that links shop with category and region

Author Comment

ID: 39880657
Do you mean my second way

Author Comment

ID: 39938994
Sorry forgot to close the question

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Without even knowing it, most of us are using web applications on a daily basis.  In fact, Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We generally confuse these web applications to…
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question