数据库设计和建模具体关系 [英] Database design and modeling specific relationships

问题描述

我想要实现运送和跟踪解决方案。

I am trying to implement a shipping and tracking solution.

有 TRUCKS $ c>产品从 CITY 转到另一个 CITY > PATHS 在特定 SCHEDULES 上。 TRUCKS 通过 CONTAINERS 运输 PRODUCTS 。通过 JOBS 指示这些 TRUCKS ，如 pick_up code> transfer ， drop_off 。

There are TRUCKS that move PRODUCTS from a CITY to another CITY via designated PATHS on specific SCHEDULES. The TRUCKS transport the PRODUCTS via CONTAINERS. These TRUCKS are instructed via JOBS, like pick_up, transfer, drop_off.

我遇到的问题是 product_1 需要移动 truck_1 通过 pick_up JOB 从 city_A 到 city_C 通过 city_B 。与此同时， product_2 被 truck_2 通过 JOB of pick_up 从 city_C 到 city_A via city_B 。 卡车可以转移（ JOB ） $ c> container （基本上是产品，只是回到他们的起源 city ，即 truck_1 ，返回 city_A 和 truck_2 ，通过 drop_off （ JOB ）的命令返回到 city_C ）。

The problem that I am having is when product_1 needs to be moved by truck_1 via a pick_up JOB from city_A to city_C via city_B. At the same time, there is product_2 being moved by truck_2 via a JOB of pick_up from city_C to city_A via city_B. Both trucks can transfer (JOB) their containers (and essentially the product and just head back to their origin city, i.e. for truck_1, back to city_A and for truck_2, back to city_C via a command of drop_off (JOB).

因此，我有以下表格：

• TRUCK （truck_id，truck_code，...）


• PRODUCT （product_id，product_code，product_name，...）


• CONTAINER （container_id，container_desc，...）


• CITY （city_id，city_name，city_desc，... ）


• PATH （path_id，from_city_id，to_city_id，...）
（transaction_id，transaction_name，transaction_desc，...） / li>

• TRUCK (truck_id, truck_code, ...)
• PRODUCT (product_id, product_code, product_name, ...)
• CONTAINER (container_id, container_desc, ...)
• CITY (city_id, city_name, city_desc, ...)
• PATH (path_id, from_city_id, to_city_id, ...)
• SCHEDULE (schedule_id, schedule_name, schedule_desc, start_time, end_time, ...)
• TRANSACTION (transaction_id, transaction_name, transaction_desc, ...)

如何建模上述场景，其中 CONTAINERS code> TRUCKS ？

How could I model the above scenario where the CONTAINERS are transferred between the TRUCKS?

推荐答案

据推测，一辆卡车和/通过一系列事件，包括遵循一个路径，并进行交付和交易等。推测一个工作是这样一个事件，其中有几种，例如取货，转移和放弃。

Presumably a truck and/or trucker has an assignment involving going through a sequence of events that including following a path and making deliveries and transactions, etc. Presumably a job is such an event, of which there are several kinds, eg pickup, transfer and dropoff.

关系数据库中的表描述了应用程序的状态。每个表都有一个关联的fill-in-the-（named-）blanks语句（谓词）。

The tables in a relational database describe the state of an application. Each table has an associated fill-in-the-(named-)blanks statement (predicate).

// truck [truck_id] has code [truck_code] and ...
TRUCK (truck_id, truck_code, ...)
// product [product_id] has code [product_code] and name [product_name] ...
PRODUCT (product_id, product_code, product_name, ...) 

（谓词表示应用程序关系， aka关系，由表，aka关系表示，因此是相关模型。）

(A predicate characterizes an application relationship, aka relation, represented by a table, aka relation, hence "the relatonal model".)

谓词的参数是表的列。当为每个参数提供值时，您将获得对应用程序为true或false的语句（命题）。列的值行为每个指定的空白提供此类值。使表的谓词为真的行在表中。如果假的话，行保留。这就是数据库状态如何描述应用程序的情况。你必须知道表语句才能读取或查询数据库，以便根据其行查明情境的真假，并通过在观察情境后精确地将真正的命题放入其中来更新数据库。

The parameters of the predicate are the columns of the table. When you supply values for each parameter you get a statement (proposition) that is true or false about your application. A row of values for columns gives such values for each named blank. The rows that make a table's predicate true go in the table. The rows that make if false stay out. That is how the database state describes the the application situation. You have to know the tables' statements in order to read or query the database to find out per its rows what is true and false about a situation and to update the database by putting exactly the rows that make true propositions into it after observing the situation.

每个查询也有一个从其表的谓词构建的谓词。两个表的JOIN给出了满足其谓词AND的UNION的行，UNION OR等。

Every query also has a predicate built from the predicates of its tables. The JOIN of two tables gives the rows that satisfy the AND of their predicates, UNION the OR, etc.

（约束对此不相关;它们只是共同描述数据库）

(Constraints are irrrelvant to this; they just collectively describe the database states that can arise given the predicates and the applcation states that can arise.)

您需要决定足够的谓词，以便能够充分描述您的应用程序的状态。这包括抽象的东西，如路由，事务和事件，日程表和赋值等。（一旦我们有足够的谓词/表，我们通过标准化等技术改进它们。）

You need to decide on sufficient predicates to be able to fully describe the the stituations of your application. This includes abstract things like routes and transactions and events and schedules and assignments etc. (Once we have sufficitent predicates/tables we improve them via techniques like normalization.)

可以是不同类型的东西，我们谈论超类型和子类型，并看到谓词（我会使用工作，我采取的是一个事件）：

When there can be different kinds of things we talk about supertypes and subtypes and see predicates like (I'll use "job" which I take to be an event):

// job [job_id] for trucker [trucker_id] is ... stuff about all jobs ...
JOB(job_id, trucker_id...)
// job [job_id] is a pickup with ... stuff about pickups ...
PICKUP(job_id, container_id...)
// job [job_id] is a transfer with ... stuff about transfers
TRANSFER(job_id,...)
...

可能不具有作为具有两个或更多个关联容器的事件的转移的不同或另外的概念等）（搜索子类型。例如。）

(You may or may not have a different or additional notion of transfer as an event with two or more associated containers, etc.) (Search "subtypes". Eg.)

这篇关于数据库设计和建模具体关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！