挑战性的数据建模问题 [英] Challenging data modeling problem

问题描述

我有一个表来存储客户项目。该表格需要反映以下活动：

1. 当客户向他/她的队列添加项目时。
$ b $



>

工作流程如下：

$ b
1. 员工创建发货标签，然后将商品标记为发货（到信息点3）
   ---客户收到料品，客户​​很高兴。












客户的帐户应显示以下内容：





2. 标记为退货的项目（如果客户想要将产品保留更长时间，则允许客户取消）。



在我的方案中没有值用于在项目在我们的末端标记为运送时提醒客户。 p>



我最初想到继承，但事情已经开始迅速摆脱。我不知道我是否应该有一个子类为每个信息点。另外，有些事情似乎不应该在自己的子类中（标记为运输的项目，返回，似乎是介于类之间）





我应该使用继承并为每个信息点创建一个类吗？



<$ p
$ p> items
- id
- typeid（在队列，发货等等）
- userid
- itemid
- shippedat
- returnedat


我使用mySql， ！

解决方案

这似乎需要一个很好的方法，适用于他们。一种方法可以如下：






• 定义项目的表，最低限度包含 userid 和 itemid


• 定义工作流状态包含 id 和名称（例如'processing'，'shipped'）


• 定义表将项目链接到工作流状态，其中包含项目的 id 的外键和状态的 id ，以及适用于给定状态下的给定项目的元数据，例如当状态被输入或开始/完成时，在该状态处理或检验项目的人员等。






现在，除了具有特定状态字段，例如 shippingdate code> returneddate 在items表上，你可以通过加入一个给定的项目到它的工作流（警告：pseudocode ahead！）找到数据：

  SELECT item.id，item.userid，item_workflow.completed_date AS returned_date 
 FROM item 
 JOIN item_workflow 
 JOIN工作流程
 WHERE workflow.state ='Returned'
  

通过将项目加入到他们的工作流程和过滤特定状态，查找处于或不在给定状态的项目，并对每个状态计数项目。




对于
，没有值（在我的方案中）提醒客户




在这个模式中，您搜索给定的项目用户，并查看该项目是否存在item_workflow记录，以及工作流状态是否为已发货。




I have a table that stores customer items. The table needs to reflect the following activities:

1. When a customer adds an item to his/her queue.
2. When the item is marked for shipping to the customer.
3. When the item actually ships.
4. When the item is marked for return by the customer.
5. When a return shipping label is created for the item.
6. When the item is received back from the customer.

The work flow will be as follows:

1. User(s) add item(s) to list.
2. Scheduled event marks items for shipping (corresponds to info point 2).
3. Employee creates shipping labels, then marks items as shipped (to info point 3) --- Customer receives item, customer is happy.
4. Customer marks item for return.
5. Employee creates a shipping label the item.
6. Item gets picked up and returned.

A customer's account should show the following:

1. Items in queue.
2. Items shipped.
3. Items marked for return (to allow customer to cancel if customer wants to keep the item for longer).

There is no value (in my scenario) for alerting the customer when an item has been marked for shipping on our end.

I initially thought of inheritance, but things have started to get out of handle quickly. I don't know if I should have a subclass for each info point. Also, some things seem like they should not be in their own subclass (items marked for shipment, and for return, seem to be "in between" classes)

Should I be using inheritance and create a class for each information point? Or am I over complicating this?

Current schema

items
   - id
   - typeid (in queue, shipped, etc....)
   - userid
   - itemid
   - shippedat
   - returnedat

I'm using mySql, and anticipate the table to be large!

解决方案

It sounds like you need a good way to separate the items themselves from the workflow which applies to them. One approach could be as follows:

• Define a table of items, minimally containing the userid and itemid
• Define a table of workflow states, minimally containing an id and a name (e.g. 'processing', 'shipped')
• Define a table linking items to workflow states, containing a foreign key to the item's id and a foreign key to the state's id, plus metadata applicable to the given item in the given state, such as when the state was entered or started/finished, who handled or checked-off the item in that state, etc.

Now, instead of having state-specific fields such as shippeddate and returneddate on the items table, you can find that data by joining a given item to its workflow (warning: pseudocode ahead!):

SELECT item.id, item.userid, item_workflow.completed_date AS returned_date
FROM item
JOIN item_workflow
JOIN workflow
WHERE workflow.state = 'Returned'

By joining items to their workflow and filtering for specific states, you can find items that are or are not in given states, and count items per state. So to address an example from your question:

There is no value (in my scenario) for alerting the customer when an item has been marked for shipping on our end.

In this schema, you search for a given item for a given user, and see if an item_workflow record exists for that item and the workflow state "Shipped".

这篇关于挑战性的数据建模问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！