多对多基本方向数据库方案设计

Question

我是数据库设计的新手，可以使用一些关于如何为学习项目设置模式的建议。这个想法就像Zork这样的基于文本的冒险游戏。

以Room数据库表为例：

class Room(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(64), index=True)
    description = db.Column(db.String(512))

    north = db.Column(db.Integer, db.ForeignKey('room.id'))
    east = db.Column(db.Integer, db.ForeignKey('room.id'))
    west = db.Column(db.Integer, db.ForeignKey('room.id'))
    south = db.Column(db.Integer, db.ForeignKey('room.id'))

当用户在一个房间里时，他们可以向南、向北等等。这些房间是链接在一起的。我觉得让外键成为表的一部分是错误的。我正在使用postgres和sqlalchemy。我觉得有一种方法可以设计这些链接，而不需要手动管理房间是否从数据库中删除等等。

links_table = db.Table('directions',
    db.Column('north_id', db.Integer, db.ForeignKey('room.id')),
    db.Column('west_id', db.Integer, db.ForeignKey('room.id')),
    db.Column('east_id', db.Integer, db.ForeignKey('room.id')),
    db.Column('south_id', db.Integer, db.ForeignKey('room.id')))

这样的事情是不是朝着正确的方向迈出了一步？或者我需要一张north_to_south的桌子，south_to_north？

 north_to_south = db.Table('north_to_south',
    db.Column('north_id', db.Integer, db.ForeignKey('room.id')),
    db.Column('south_id', db.Integer, db.ForeignKey('room.id')))

Answer 1

不完全确定SQL DB是否是用于存储这类地图的合适工具，但是您的思路是正确的，因为方向可以使用association object模式存储在association table中，或者用SQLAlchemy的术语来说就是association object。您可以通过创建关联表(如(src_id, dst_id, dir) )来使模型更加灵活

class Direction(db.Model):
    # Let the DB cascades remove directions, if a room is deleted.
    src_id = db.Column(db.ForeignKey('room.id', ondelete='CASCADE'),
                       primary_key=True)
    dst_id = db.Column(db.ForeignKey('room.id', ondelete='CASCADE'),
                       primary_key=True)
    # A point for further normalization, create a table for enumerating
    # direction labels.
    dir = db.Column(db.String(16), primary_key=True)

    # Since this model has multiple foreign keys referencing the same
    # target model, the relationships must be told which one to use.
    src = db.relationship('Room', foreign_keys=[src_id],
                          back_populates='directions')
    dst = db.relationship('Room', foreign_keys=[dst_id])

这也将允许您使用另一个很好的SQLAlchemy特性：mapping a collection of related objects as a dictionary

from sqlalchemy.orm.collections import attribute_mapped_collection

class Room(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(64), index=True)
    description = db.Column(db.String(512))

    directions = db.relationship(
        'Direction', collection_class=attribute_mapped_collection('dir'),
        foreign_keys='[Direction.src_id]', passive_deletes=True,
        back_populates='src')

填充方向有点繁琐：

In [33]: r1 = Room(title='r1')

In [34]: r2 = Room(title='r2')

In [35]: r3 = Room(title='r3')

In [36]: r4 = Room(title='r4')

In [37]: r5 = Room(title='r5')

In [39]: for dir_, dst in zip(['north', 'east', 'south', 'west'],
    ...:                      [r2, r3, r4, r5]):
    ...:     r1.directions[dir_] = Direction(dir=dir_, dst=dst)
    ...:     

In [40]: db.session.add(r1)

In [41]: db.session.commit()

请注意其中的细微冗余，因为您必须使用方向作为键并将其传递给Direction构造函数。理想情况下，您根本不必与Direction打交道。这可以使用association proxy来实现

from sqlalchemy.ext.associationproxy import association_proxy

class Room(db.Model):
    ...
    dirs = association_proxy(
        'directions', 'dst',
        creator=lambda dir_, dst: Direction(dir=dir_, dst=dst))

在此之后，添加方向会更容易：

In [87]: r6 = Room(title='r6')

In [88]: r2.dirs['up'] = r6

这使得在给定变量的情况下访问方向变得很容易：

In [79]: where_to = input()
west

In [80]: r1.dirs[where_to]
Out[80]: <__main__.Room at 0x7fa652e4a320>