如何在双边市场中建立关系表 [英] How to build relational tables in a two-sided marketplace

问题描述

我真的在努力解决这个问题，会喜欢一些额外的想法。这是基本的上下文：

• 用户都可以列出项目被借出，并使请求用于借款


• 请求由用户发布，谁想借用东西。每个请求可能包含几个项目


• 项目是预定义的（即，POST表单是复选框），并由用户列出，他们想借出它们，并依次借用其他用户谁提交了请求

工作流程：

1. John是一个用户，并提交了从6/5到6/8的帐篷请求。 >
   
2. 控制器查找从6/5到6/8可用的帐篷的所有用户（除了John）


3. 此用户列表与看谁想为约翰提供一个帐篷


4. 谁肯定答复约翰先生，他们的项目在表中自动更新为不再可用从6/5到6/8


5. John和其他用户连接进行交换

到目前为止，我的想法是： p>

• 用户表 has_many：items 和 has_many：requests


• 请求表 belongs_to：users


• 项目表 belongs_to：users

我的大脑似乎无法处理的其他复杂性：

• 一个请求可以包含多个项目，我已经告诉反对在一个单元格中接受项目作为序列化数组，所以我不知道如何将请求和项目。在上面的表格中，应该是项目还 belongs_to请求？如果是，这似乎意味着用户必须为请求指定项目，而我希望用户能够搜索帐篷，并查看有帐篷可用的所有用户的列表。




• 请求包含 start_date 和 end_date 属性，在某种程度上需要与项目可用时进行比较。现在我正在考虑项目表，需要有一列存储项目将被使用的日期（即不可用）。但是这个数据再次是一个数组。例如，可能会要求帐篷（并且用户响应OK）从6/5到6/8，然后再次从6/10到6/15，然后再从7/8到7/9。所以我需要第四个表格？




• 项目将是预定义的列表，例如帐篷，睡袋，睡垫。这样，我想知道我实际上需要一个 has_and_belongs_to_many 与用户的关系，因为帐篷可能属于许多用户和用户可以拥有许多帐篷。

对不起，如果这听起来像一个匆忙...我已经坐在这里4个小时，纸张和涂鸦，这并不清楚...

解决方案

您需要了解相关记录信息的基本方式，即在表格中。

查找足够的表

说明您需要描述业务情况：

 用户（user_id，name，...）
 //用户[user_id]被命名为[name] 
联系人（contact_id，item，offer_id）
 //用户[contact_id]被联系了[用户]提供的[item_id] 
 ...等等... 
  

语句的参数是表的列。

如果你想谈谈你认为有多个部分（异质或同质）的部分，这只是意味着一些陈述将涉及到一件事情及其部分：

 表请求（request_id，start_date，end_date，...）
 // [request_id]从[start_date]到[ end_date]和... 
表请求（request_id，item_id，person_id，...）
 // person [person_id]请求项[item_id]请求[request_id] 
  

表中有什么

表的值是使其语句为真的行。 （每个查询子表达式也有一个语句，其值是使其语句为真的行。）

不要将表语句与业务规则混淆。业务规则说明真相。但是一个表语句是一些语句，一些元组是真的（并且在表中）或者false（不在表中）。表中的所有真实和虚假的陈述告诉您需要了解的关于业务的一切。业务规则绝对不会与之相矛盾。 （因为它们总是真的）。​​

重新排列成更好的表

一个键是一组列，所有其他列都是这样的函数，但是没有一个子集具有该属性。表可以有多个键。

为了使数据库更容易更新和查询，您应该分解某些语句，这些语句是由AND加入的其他语句。分解，直到每个语句只包含一个关于这个表单语句的关键列的语句：

  [my_column] = my_function [key_k_column_1]，[key_k_column_2]，...）
  

其中key_k_column_1，...是相同的键key_n。

（这样的表格是第五种正常形式，主题是规范化。）

I'm really struggling with this problem, would love some additional thoughts. Here's the basic context:

• Users can both list items to be lent out, and make requests for items to borrow
• Requests are posted by users who want to borrow something. Each request may contain several items
• Items are predefined (i.e., POST form is a checkbox) and listed by users who want to lend them out, and are in turn borrowed by other users who have submitted a request

Workflow:

1. John is a user and submits a request for a tent from 6/5 to 6/8
2. The controller looks for all users (besides John) who own tents that are available from 6/5 to 6/8
3. This list of users are contacted to see who wants to provide a tent for John
4. Whoever responds affirmative to John first has their item in the table automatically updated to be no longer available from 6/5 to 6/8
5. John and the other user are connected to make the exchange happen

So far my thinking:

• Users table has_many :items and has_many :requests
• Requests table belongs_to :users
• Items table belongs_to :users

Additional complexities that my brain can't seem to process:

• One request can contain multiple items, and I've been told against accepting items as a serialized array in one cell, so then I'm not sure how to relate request and item. In the tables above, should items also belong_to requests? And if yes, this seems to imply that a user has to make a request for a specific item whereas I want the user to be able to search for tent, and see a list of all the users who have tents that are available

• Requests contain start_date and end_date attributes, that somehow need to be compared to when the item is available. Right now I'm thinking in the items table, there needs to be a column that stores the dates when the item will be in use (i.e., not available). But then this data will be an array again. For example, a tent might be requested (and the user responds OK) from 6/5 to 6/8 and then again from 6/10 to 6/15, and then again from 7/8 to 7/9. So do I need a fourth table??

• Items will be a predefined list, e.g., tent, sleeping bag, sleeping pad. In this way, I'm wondering do I actually need a has_and_belongs_to_many relationship with users, since a tent could belong to many users, and a user could have many tents.

Sorry if this sounds like a ramble... I've been sitting here for 4 hours with many sheets of paper and scribbles and this is not getting any clearer...

解决方案

You need to understand the basic way of recording information relationally ie in tables.

Finding sufficient tables

Just have a base table for every statement you need to describe a business situation:

User(user_id,name,...)
    // User [user_id] is named [name]
Contacted(contact_id,item,offer_id)
    // user [contact_id] was contacted re item [item] offered by user [offer_id]
...etc...

The parameters of the statement are the columns of the table.

If you want to talk about the parts of something that you think of as having multiple parts (heterogenous or homogeneous) that just means that some statements will involve a thing and its parts:

table request(request_id,start_date,end_date,...)
    // [request_id] goes from [start_date] to [end_date] and ...
table requested(request_id,item_id,person_id,...)
    // person [person_id] requested item [item_id] in request [request_id]

What's in a table

A base table's value is the rows that make its statement true. (Every query subexpression also has a statement, and its value is the rows that make its statement true.)

Don't confuse table statements with business rules. Business rules state truths. But a table statement is a statement that some tuple makes true (and goes in the table) or false (is left out of the table). All the true and false statements from the tables tell you everything you need to know about the business. The business rules will never contradict them. (Since they're always true.)

Rearranging to better tables

A key is a set of columns that all other columns are such functions of but none of whose subsets have that property. A table can have more than one key.

To make a database easier to update and query you should break up certain statements that are other statements joined by AND. Break up until each statement consists of a statement only about key columns ANDed with statements of this form:

[my_column]=my_function([key_k_column_1],[key_k_column_2],...)

where key_k_column_1,... are columns of the same key key_n.

(Such a table is "in fifth normal form" and the topic is "normalization".)

这篇关于如何在双边市场中建立关系表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！