0

I'm looking for guidance on the database structure for a multi-regional website.

I am setting up a website, similar to craigslist.com, that will allow users to post adds in their city. I am using a MySQL database.

I want to have regions as subfolders linked to subdomains e.g. ca.mysite.com goes to mysite.com/ca.

My question is how do I manage the database(s) when I want to add another city to my website.

If I use a different database for each city, then users wont be able to login to other cities as their login details are stored in the city they registered at in the users table of it's db.

This may not be an issue though, as the content is city specific, like craigslist. But, should they wish to contact users in other cities, they wont be able to.

Also, there would be duplicate username's and email addresses overall, as users could register in all of the cities using the same email and username.

If I create a central database with for instance, a users table, and a messages table, and then a separate db for each city with all that cities 'posts' then when displaying basic information I will need to consult the city specific db plus the central db that stores user info.

Alternatively I could store everything on one database, and store the logged-in user's location in a cookie, pass that to a session variable, and use that location as part of the database query when displaying search results etc.

However, would this not add an unnecessary overhead to each query?

e.g. every search would have to have ' AND location = $user_location ' added in.

I really have no idea what would be the best method here.

Thanks in advance for any help on this.

Aidan
  • 21
  • 3

2 Answers2

0

It seems that you still do not know clearly the system you want to build. Before starting designing a database for this system, please make sure that you have the full description of the system requirements. Some sample questions that help you clarify are:

  • What features does your website offer?
  • What are all the actions that a user using your system can do? With each action, is there any restrictions?

Some other questions relating to the system performance: - How many users do you expect to use your system? - How fast and correct each action should be served? Which actions are used frequently?

It makes no sense to design a system without a careful understanding about the system requirements.

Hieu Nguyen
  • 382
  • 2
  • 15
  • Thanks Hieu. I didn't want to list everything as the question is already quite long. I just wanted some direction, using a real world example, such as craigslist, or a smaller example that a stackoverflow user may have experience of building.The main features of the site are, posting adverts (which include text and up to 8 images). I anticipate around 10,000 active adverts in each city at any one time. Each user can message each other using a simple internal messaging system (all handled in 'messages' table). They can also post ads. That is the brunt of it. The key point is multi-city. – Aidan Nov 05 '14 at 22:07
  • If you plant to use MySQL, which is a relational database, I suggest to use one table for users (let's call it Users), one table for cities (Cities), one table for ads (Adverts), and one table for messages (Messages). The decision above based on my understanding that a user belongs to only one city but he can freely to switch between cities and posts ads on any city he wants. Also, users may send messages without friendship set up. – Hieu Nguyen Nov 06 '14 at 00:46
  • Yes, that is the basic setup, of course, but what I am asking, is, what is the NORM for setting up websites with multiple regions (cities) like craigslist? Will searching through 100,000 ads (when I have 10 cities on one db), as opposed to 10,000 ads (when there are 10 cities, and 10 individual dbs) be better, and if so, how can I maintain the link between sites in relation to the users table? – Aidan Nov 06 '14 at 01:20
  • It is not a problem for a relational database to have 100k ads in a table. By applying indexes on necessary fields, the DBMS can return result set very fast even if you have a million rows. However, if we are talking about a billion rows, the design may be different to adapt. That's why I say the requirements are crucial to the design. The design is just a solution to satisfy the requirements. If any design satisfies the requirements, it is a good design. – Hieu Nguyen Nov 07 '14 at 08:22
0

So here is the sample database design and how it can support your requirements.

Cities(cityid, cname)
Users(userid, fname, lname, cityid, currcityid)
Messages(mid, senderid, receiverid, content)
Adverts(aid, title, content, userid, cityid)

When a user switches the city, update the currcityid field in its row in the Users table.

When a user posts an ad on a city, insert a new row to the Adverts table. The userid and cityid of the new row are the ids of the corresponding user and city.

When a user sends a message to another user in the system, add a row to the Messages table. The senderid and the receiverid of the new row are the ids of the corresponding users.

Query all ads in a city: SELECT * FROM Adverts WHERE cityid = $cityid

Query all ads of a user: SELECT * FROM Adverts WHERE userid = $userid

Query all ads of a user in a specific city: SELECT * FROM Adverts WHERE cityid = $cityid AND userid = $userid

Hope this helps.

Hieu Nguyen
  • 382
  • 2
  • 15
  • So is this how Craigslist for example organizes the various cities in each location? I would have thought it was more complicated. – Aidan Nov 06 '14 at 01:22
  • Of course the design of Craiglist is more complicated because it offers a lot of features. This is just a sample design and it is sufficient enough for your requirement. The more requirements you have, the more subtle design you may have. – Hieu Nguyen Nov 07 '14 at 08:11