duancao2082 2016-01-01 03:09
浏览 18
已采纳

使用PHPMyAdmin并创建一个包含多个条目的列? [关闭]

I'm creating a database for the first time! The goal is to have a database for users. I'm using PHPMyAdmin on my localhost (XAMPP) to set it up.

This is pretty straightforward for things like "username" and "password", but one of the fields would be a list of activities that the user participates in.

So for instance, "Activities: Skiing, Rowing, Swimming" would be what this would look like on their page.

But I am not sure how to set this up on PHPMyAdmin. I can't just create a field called "Activities" of type VARCHAR(255) because I think it would store their interests in one box, which isn't good because I need to have these separatedly sorted for later access (For instance, I'd need to query all the users that have the "Swimming" activity in common).

What approach should I ideally be using?

Thank you!

  • 写回答

1条回答 默认 最新

  • dongmeiyi2266 2016-01-01 03:38
    关注

    Table users

    ---------------------------------------
    | id |  username  |     password      |
    ---------------------------------------
    | 1  |    john    |  somehashedpass1  |
    | 2  |    carl    |  somehashedpass2  |
    | 3  |    maria   |  somehashedpass3  |
    ---------------------------------------
    

    Table activities

    --------------------------------------
    | id |  user_id  |     activity      |
    --------------------------------------
    | 1  |     1     |      Skiing       |
    | 2  |     1     |      Rowing       |
    | 3  |     1     |     Swimming      |
    | 4  |     2     |      Skiing       |
    | 5  |     2     |      Rowing       |
    | 6  |     3     |      Skiing       |
    --------------------------------------
    

    Then use JOIN or Sub-queries to fetch the required data.

    Also, you could implement a 3-tables schema to define users, activities and relationships, like this:

    Table users (no variation)

    ---------------------------------------
    | id |  username  |     password      |
    ---------------------------------------
    | 1  |    john    |  somehashedpass1  |
    | 2  |    carl    |  somehashedpass2  |
    | 3  |    maria   |  somehashedpass3  |
    ---------------------------------------
    

    Table activities

    -----------------
    | id | activity |
    -----------------
    | 1  |  Skiing  |
    | 2  |  Rowing  |
    | 3  | Swimming |
    -----------------
    

    Table relationships

    ------------------------------
    | id | user_id | activity_id |
    ------------------------------
    | 1  |    1    |      1      |
    | 2  |    1    |      2      |
    | 3  |    1    |      3      |
    | 4  |    2    |      1      |
    | 5  |    2    |      2      |
    | 6  |    3    |      1      |
    ------------------------------
    

    This will be basicaly the same as the previous implementation, but you'll have a better structured database.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题