5 Replies - 697 Views - Last Post: 06 March 2012 - 04:57 PM

#1 Polofiesta  Icon User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 78
  • Joined: 15-December 10

Optimization Help

Posted 05 March 2012 - 09:37 PM

Hi so I am making a small website and I have a question about which one is more efficient in accessing data because my site is mainly based on a search engine. Now people are going to search by there city and get criteria from there about the books. So will it be more efficient to:
1) Have a separate database for each country - TABLES: bookInfo (containing all the book info and what city it is for) and users(containing userID, city)
2) Have a separate database for each country - TABLES: a bookInfo table each for a city. and a users table

So what I am trying to say is this: is it better to have more tables? more databases? or everything in one table?

Is This A Good Question/Topic? 0
  • +

Replies To: Optimization Help

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9379
  • View blog
  • Posts: 35,225
  • Joined: 12-June 08

Re: Optimization Help

Posted 05 March 2012 - 10:59 PM

No from what I am trying to read neither. Have a table for cities (or countries? - you are quite hard to follow on this point). Have a table for books. Have a table that matches book ids to city ids... then have your user table.. Though I am sure how the user table works with the rest - that was never made clear.
Was This Post Helpful? 0
  • +
  • -

#3 Polofiesta  Icon User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 78
  • Joined: 15-December 10

Re: Optimization Help

Posted 06 March 2012 - 01:36 PM

Ok sorry. I will have a separate database for each country. Now this is what I have for my database for Canada.

TABLE #1 "OntarioBooks"
Fields/Columns = bookID, title, author, isbn, userID(which is joined with another table), city

TABLE #2 "Another Province"
Fields/Columns = bookID, title, author, isbn, userID(which is joined with another table), city

ETC.

So whenever a person chooses there location I just connect to that table and thats it, so it will be faster searching inside a table which is specifically for a province instead of a whole country. Now the only other table with be Users, which will have just user information and there location. Will this be a smart way going about it? and please tell me if you still don't get what I'm saying.
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9379
  • View blog
  • Posts: 35,225
  • Joined: 12-June 08

Re: Optimization Help

Posted 06 March 2012 - 01:40 PM

Quote

Will this be a smart way going about it?

No. As I said - three tables. One for the locations, one for the books, and one that matches up locations to books.
Was This Post Helpful? 0
  • +
  • -

#5 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: Optimization Help

Posted 06 March 2012 - 02:18 PM

You need to read up about database normalisation. You want to reach 3NF (third normal form) to have a well normalised database. To assist you, here are the Wikipedia pages on 1NF, 2NF, and 3NF, and a tutorial by Atli.

Now, as a solution to your problem, I would suggest something along these lines:
    Countries
    ID (PK)
    Description

    States
    ID (PK)
    CountryID (FK -> Countries.ID)
    Description

    Cities
    ID (PK)
    StateID (FK -> States.ID)
    Description

    Users
    ID (PK)
    CityID (FK -> Cities.ID)
    + any other information, such as login info, or their first and last names etc

    Authors
    ID (PK)
    FirstName
    LastName

    Books
    ID (PK)
    CityID (FK -> Cities.ID)
    Title
    AuthorID (FK -> Authors.ID)
    ISBN


This schema will give you a fair amount of leeway when it comes to search types. I would suggest moving the Countries/States/Cities into a single Locations table, but that's getting into the more advanced topic of managing hierarchical data, which is unnecessary at this stage.

This post has been edited by e_i_pi: 06 March 2012 - 03:16 PM

Was This Post Helpful? 1
  • +
  • -

#6 Polofiesta  Icon User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 78
  • Joined: 15-December 10

Re: Optimization Help

Posted 06 March 2012 - 04:57 PM

Well here is my structure of my database. I am going for optimization because I need to have my search results be as efficient as possible and as quickly as possible. So I need the way in which MYSQL can access the data the quickest. Here it is:

bookInfo
bookID(PK)
userID(FK -> users)
cityID(FK -> cities)
+ additional book info

users
userID(PK)
provinceID(FK - province)
cityID(FK - cities)

cities
cityID(PK)
name
provinceID(FK - province)

province
provinceID(PK)
name

Now that is my final database. And if you would be able to is there any sort of link or tutorial about managing hierarchical data. My whole website is based on a search engine so I need the best way to organize my data so the search results can come up the fastest even with high traffic.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1