Solved

MySQL Database-Table setup

Posted on 2014-02-22
5
258 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
  • 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Updating a field based from a if exist.... 2 34
SQL - Simple Pivot query 8 27
MySQL Persistent Connections 10 35
Not listening to where 1 22
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
JavaScript has plenty of pieces of code people often just copy/paste from somewhere but never quite fully understand. Self-Executing functions are just one good example that I'll try to demystify here.
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…

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