MySQL multiple tables vs rows

Question

I have read many posts on here about this discussion but my question is specific.

Please read in entirety before replying.

I am wondering whether it is best to have potentially hundreds if not thousands of rows in a database or to split it across multiple tables.

The scenario is: I have a user who can be in ONE AND ONLY ONE city at a time. (There are multiple cities e.g. Paris, Berlin and London) Now in each city are lots of areas (potentially several hundred). I was thinking that giving each city its own table would be more efficient.

E.G:
rooms_london
- All areas of london in here as rows
rooms_berlin
- all areas of berlin in here as rows

And so on for Paris and any other cities that I add in future.

Then in PHP I could construct a query similar to:

 SELECT * FROM rooms_$playerCity WHERE roomID = $playerRoom

Is this an efficient method or should I just add an extra column to a central rooms table.

If I've not been clear enough I will do my best to clarify anything that you need.

Many Thanks

"The scenario is: I have a user who can be in ONE AND ONLY ONE city at a time." Try using DISTINCT. — Funk Forty Niner
– Funk Forty Niner, Commented May 8, 2014 at 13:35
Are you asking how to build the tables and relationships to handle this scenario? If you are limited to working with a schema that has already been built, please share the definitions for the User (player), City and Area tables. — bsivel
– bsivel, Commented May 8, 2014 at 13:41
It would most certainly NOT be more efficient to create a separate table for each city. — Strawberry
– Strawberry, Commented May 8, 2014 at 13:42
SELECT * FROM rooms_$playerCity WHERE roomID = $playerRoom beware of SQL injection — Imre L
– Imre L, Commented May 8, 2014 at 13:43
@ImreL I would use the proper escaping sequences etc to prevent injection. bisvel I'm not stuck to an existing schema it's a new system I'm building — Ben
– Ben, Commented May 8, 2014 at 14:12

Imre L · Accepted Answer · 2014-05-08 13:49:14Z

2

I would not split rooms into different tables.

If performance becomes a problem I would use partitioning http://dev.mysql.com/doc/refman/5.5/en/partitioning.html

+------+          +------+
| City | 1 ---- * | Room |
+------+          +------+

whether ... thousands of rows in a database or to split it across multiple tables.

performance will not be a problem before few million rows with complex queries and insufficient indexing

edited May 8, 2014 at 13:49

answered May 8, 2014 at 13:41

Imre L

6,29126 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ben Over a year ago

I never came across partioning before.

bsivel · Accepted Answer · 2014-05-08 15:32:06Z

0

I don't use MY SQL but my SQL Server table design would look like this

Create Table Player (
  PlayerID int
  ...
  )

 Create Table City (
   CityID int
   ...
  )

 Crate Table Rooms (
  RoomID int
  CityID int
  ...
  )

  Create Table PlayerRoom (
    PlayerID int
    RoomID int
    ChaeckIn DateTime
    CheckOut DateTime
    ...
    )

enter image description here

Then to get the last player in a room you could query by

  Select Top 1 * from PlayerRoom where Roomid = @roomID order by CheckIn Desc

answered May 8, 2014 at 15:32

bsivel

2,9715 gold badges28 silver badges32 bronze badges

Collectives™ on Stack Overflow

MySQL multiple tables vs rows

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related