How to model a Letter Transportation business context in a database diagram?

I am trying to crate entity-relationship diagram for a transport company database. This database should store information about letters, deliveries, moving letters from one warehouse to another, and historical information. It should allow tracking letters, e.g., checking what letters were on certain date in a specific warehouse.

The process I want to model looks like this:

• letter is collected from sender and it goes to warehouse,

• then it could be transferred between warehouses,

• at last, letter is delivered to recipient.

Business rules

• Letters are sent and received by People, one letter can be sent by one particular Person, and also received by a specific Person.

• There can be many People in database, if a Person is stored in the database it must be Sender or Receiver of some Letter.

• Transports are made between different Warehouses and must consist of many Letters, Letters can be a part of multiple Transports (on different Dates)

• Warehouses store multiple Letters

• A Letter can be stored in few different Warehouses on different Dates (on ‘01.11’ it is in ‘Warehouse A’, and on ‘02.11’ in ‘Warehouse B’)

• DateStorage - contains information about when a Letter has arrived to certain Warehouse

• Transports are being done on certain Date, they depart from one Warehouse and go to another.

• Letters can be delivered to recipients. If Delivery Attempt fails - there could be more attempts.

Current diagram

This is the database diagram I have created so far:

Relationship between Letters and Warehouses means that when letter is picked up by courier it is put to that warehouse.
Questions and current considerations

Is this diagram correct, what could I improve?

Is it OK that there are to relationship between transports and warehouses (from could be deduced from previous transports and initial warehouse of letter)?

Although you stated that the structure depicted in your diagram is part of a university project, the objective should be to make it as realistic as possible, so I consider that carrying out an interview (or a series of interviews) with the experts of a correspondence company might be very helpful (perhaps indispensable).

In this way, since creating a full and accurate entity-relationship diagram (or any of its derivatives) for this kind of business domain would require a much more in-depth examination (and, thus, a very long series of to-ings and fro-ings), the goal of this answer is to point you in the right direction with respect to the technical methods you might have to employ, presenting an expository analysis that includes my first draft of an IDEF1X model made up of elements that seem feasible, so that you can use it as a reference to capture the meaning of a real scenario.

Central aspects

The way I see it, the aspects of the business domain analysis that require special attention are the Events in which a Letter might be involved; therefore, I will detail bellow a hypothetical context that consists of some supossed Types of Event that appear of relevance (but, naturally, you should confirm, modify or discard all the discussed points on your own).

Considerations

I have listed the elements (brought up in the talk we had via chat) that I consider particularly helpful to work on forwarding the situation:

• [A]t any time, I would like to know where the letter is - no matter if it is in some warehouse or it is being transported.

• In my design sender is only the original sender of a letter (client of the company) and receiver (recipient) is a person to which letters is addressed - there is always one sender and one recipient for a letter.

• [F]or one transport there can only be one courier.

• One letter can be stored in up to about 10 warehouse. However number of all warehouses is much higher - few hundreds (or maybe even thousands).

• My idea was to store every client (sender / recipient) in database. So if some person have send or received at least one letter it […] is in database (but if person sends / receives more letters it is in database only once).

Business rules

Then, based on (a) such elements, (b) the content of your question, and (c) some estimations and assumptions that appear viable, I wrote the following formulations that describe the tentative interrelationships between the hypothetical entity types of significance (i.e., a substantial part of the business context rules, which serve the purpose of defining the corresponding conceptual model):

Person, Letter and Address

• A Letter is sent by exactly one Person (who plays the role of Sender)

• A Person sends zero-one-or-many Letters

• A Letter addresses exactly one Person (who carries out the role of Addressee)

• A Person (performing the role of Addressee) is addressed in zero-one-or-many Letters

• A Person keeps zero-one-or-many Addresses

• An Address is kept by zero-one-or-many Persons

• An Address locates the Sender of zero-one-or-many Letters

• An Address locates the Addressee of zero-one-or-many Letters

Letter and Event

• A Letter is involved in one-to-many Events

• An Event is classified by exactly one EventType

• An Event is

• either a Dispatch1

• or an Exit2

• or a Transport3

• or a HalfwayStorage4

• or a DeliveryAttempt5

• or a Receiving6

1 Dispatch. The Event that occurs when a certain Person leaves a specific Letter in a precise Warehouse so that it can take part in a transportation process.

2 Exit. The Event that happens when a particular Letter departs from a given Warehouse to start the transportation.

3 Transport. The Event that occurs when a certain Letter is being moved between distinct Warehouses while the transportation is taking place.

4 Halfway Storage. The Event that happens when a concrete Letter is kept in an exact Warehouse in the middle of the transportation.

5 Delivery Attempt. The Event that occurs when a certain Person (playing the role of Courier) tries to deliver a specific Letter to the Person who was addressed in it.

6 Receiving. The Event that happens when a certain Letter is received in the Address that locates the Person who was addressed in such Letter.

Note: Some of these Types of Event (or Event Types) might come about several times with respect to a certain Letter (e.g., a Letter could be involved in multiple Transport occurrences during a whole transportation process); other Types of Event appear to be constrained to a unique occurrence regarding a concrete transportation process (e.g., Dispatch and Receiving). On the other hand, a real business domain may entail more Event Types, and the ones discussed in this answer might simply not apply or could have distinct characteristics, that is why performing an analysis yourself is paramount. Furthermore, the terms I use here are merely explanatory, as they possibly are not equal to those employed in a

StackExchange Database Administrators Q#153833, answer score: 7