[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

MySQL Database-Table setup

Posted on 2014-02-22
5
Medium Priority
?
262 Views
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?
shoptable
shop_id(primary)   user_id  Shop_name     shop_phonenumber  shop_address shop_postalcode  shop_point
2                  11       Myshopname     12345678         Mystreet     mypostalcode      mylatlng
  
categoriestable
category_id  category_name
1              firstcategory
2              secondcategory

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

shop_categories
shop_id category_id
2        1
2        2


shop_regions
shop_id region_id
2       1
2       3


OR use one table with one column type(char) 

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

Open in new window

0
Comment
Question by:Panos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
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.
0
 
LVL 2

Author Comment

by:Panos
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.
0
 
LVL 16

Accepted Solution

by:
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
0
 
LVL 2

Author Comment

by:Panos
ID: 39880657
Hi
Do you mean my second way
0
 
LVL 2

Author Comment

by:Panos
ID: 39938994
Sorry forgot to close the question
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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 learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.

650 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