ruby - object_id 数组及其坐标在数据库中的存储方式是什么？

Question

我在 Rails 3 上有一个带有 PostgreSQL 数据库的应用程序。

问题是:我想用 object_id 存储坐标以便像这样访问它:

Post1 -> Coordinates [100,200] -> Attachment1
Post1 -> Coordinates [400,400] -> Attachment2
Post1 -> Coordinates [200,500] -> Attachment3

Post2 -> Coordinates [150,310] -> Attachment1

Post3 -> Coordinates [50,710] -> Attachment1
Post1 -> Coordinates [430,430] -> Attachment2

我想存储所有附件，并将坐标放在一个地方(即协调模型)，以免弄得一团糟。可能吗？

我从来没有遇到过任何解决这个问题或类似问题的方法。

Answer 1

这看起来像是一个包含两部分 key 的简单 m:n 关系。使用联合表对其进行相关建模。为了给你这个想法，假设每个坐标位置只能有一个 (post, attachment) 对，SQL 措辞将是:

CREATE TABLE post (
    post_id serial primary key,
    blah_other_data text
);

CREATE TABLE attachment (
    attachment_id serial primary key,
    blah_other_data text
);

CREATE TABLE post_attachment (
    post_id integer not null REFERENCES post(post_id),
    attachment_id integer not null REFERENCES attachment(attachment_id), 
    coordinate_x integer not null,
    coordinate_y integer not null,
    PRIMARY KEY (coordinate_x, coordinate_y)
);

我相信您可以将其转换回 Rails 模型和查询。

如果每个坐标位置可以有多个 (post,attachment) 对，那么您必须将 post_attachments 的 PRIMARY KEY 调整为通过将坐标列添加到键来允许这一点。

演示数据设置:

INSERT INTO post (blah_other_data)
VALUES ('Post1'),('Post2'),('Post3');

INSERT INTO attachment(blah_other_data)
VALUES ('Attachment1'),('Attachment2'),('Attachment3');

INSERT INTO post_attachment (post_id, attachment_id, coordinate_x, coordinate_y)
SELECT post_id, attachment_id, x, y
FROM (VALUES
    ('Post1', 100,200, 'Attachment1'),
    ('Post1', 400,400, 'Attachment2'),
    ('Post1', 200,500, 'Attachment3'),
    ('Post2', 150,310, 'Attachment1'),
    ('Post3', 50,710,  'Attachment1'),
    ('Post1', 430,430, 'Attachment2')
) rows(post_data,x,y,attachment_data)
INNER JOIN post ON (post.blah_other_data = rows.post_data)
INNER JOIN attachment ON (attachment.blah_other_data = rows.attachment_data);

通过连接访问数据:

SELECT p.post_id, p.blah_other_data AS post_data, 
       a.attachment_id, a.blah_other_data AS attachment_data, 
       c.coordinate_x, c.coordinate_y 
FROM post_attachment c 
INNER JOIN post p ON (c.post_id = p.post_id) 
INNER JOIN attachment a ON (c.attachment_id = a.attachment_id);
 post_id | post_data | attachment_id | attachment_data | coordinate_x | coordinate_y 
---------+-----------+---------------+-----------------+--------------+--------------
       3 | Post3     |             1 | Attachment1     |           50 |          710
       2 | Post2     |             1 | Attachment1     |          150 |          310
       1 | Post1     |             1 | Attachment1     |          100 |          200
       1 | Post1     |             2 | Attachment2     |          400 |          400
       1 | Post1     |             2 | Attachment2     |          430 |          430
       1 | Post1     |             3 | Attachment3     |          200 |          500
(6 rows)