Solved

MySQL Database-Table setup

Posted on 2014-02-22
5
259 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 500 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

726 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