duanjian7343
2014-05-08 13:32 阅读 18
已采纳

MySQL多表与行

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

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

2条回答 默认 最新

  • 已采纳
    douzhang7184 douzhang7184 2014-05-08 13:41

    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

    点赞 评论 复制链接分享
  • dqhdpppm02183 dqhdpppm02183 2014-05-08 15:32

    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
    
    点赞 评论 复制链接分享

相关推荐