Solved

MySQL Database-Table setup

Posted on 2014-02-22
5
254 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Hi
Do you mean my second way
0
 
LVL 2

Author Comment

by:Panos
Comment Utility
Sorry forgot to close the question
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
indexed table vs unindexed table 19 60
move records one table to another 14 50
mySql Syntax 7 26
complicated query 15 41
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.‚Äč
Browsers only know CSS so your awesome SASS code needs to be translated into normal CSS. Here I'll try to explain what you should aim for in order to take full advantage of SASS.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now