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)?
Best Answer
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:
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
Letter and Event
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 real organization.
Warehouse and the different subtypes of Event: Dispatch, Exit and HalfwayStorage
Vehicule and the distinct subtypes of Event: Exit, Transport and DeliveryAttempt
Person and the subtypes of Event: Transport, DeliveryAttempt and Receiving
Illustrative IDEF1X model
Consequently, I built an IDEF1Xa model in terms of all the points explained above. It is shown in Figure 1 (make sure to click the link so that you can observe it at a higher resolution):
a Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in december 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) some of the theoretical works authored by the originator of the Relational Model, i.e., Dr. E. F. Codd; on (b) the Entity-Relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.
Event and its subtypes
As demonstrated in said model, there is a supertype-subtype relationship that comes about
Event(the supertype), andDispatch,Exit,Transport,HalfwayStorage,DeliveryAttemptandReceiving(the subtypes).The
Eventsuperentity type possesses the properties that are common to all its subtypes, i.e.,LetterNumberandDateTime(exposed as a composite PRIMARY KEY [PK for brevity]), along with a discriminator, i.e.,EventTypeCode, which indicates the exact kind of subtype instance that must be complementing eachEventoccurrence.The PKs of
Dispatch,Exit,Transport,HalfwayStorage,DeliveryAttemptandReceivingare, at the same time, FOREIGN KEYs (FKs) that point to theEventPK.Every one of these subtypes displays the properties (or attributes) that apply exclusively to each of them, and some of those properties are FKs that illustrate the relationships that such subtypes have with some of the other entity types included in the model.
In this regard, you might find of help my answers to
“Modeling a scenario in which each Music Artist is either a Group or a Solo Performer”;
“How to model an entity type that can have different sets of attributes?”;
“Modelling a database structure for multiple user types and their contact information”;
and the answers grouped by the subtypes tag.
Data samples
You did not clarify if the diagram is going to be used as a conceptual platform to construct a logical database structure (with proper tables, columns, column types and constraints) on a particular SQL database management system, but some data samples in tabular form will help to supply a more rounded answer that introduces further possibilities.
An EventType table
A table standing for the
EventTypeentity type would fulfill a “look-up” role, and it may comprise the following rows:Note that the
EventTypeCodecolumn, that has to be constrained as the PK, contains values that are meaningful (and, hence, are highly legible from the end user point of view), which enhances their usage when referenced from columns with FK constraints. At the same time, since the aforementioned values are physically light (as for their size in bytes), they behave quite fast regarding data retrieval processes and optimizes disk space consumption (mainly when pointed to from FKs).An Event table
Then, let us suposse that a table dennoting the entity type named
Eventmaintains the data points that follow for the Letter that is primarily identified by the Number 1750:As shown, that table would be retaining a sequence of all the Events in which Letter Number 1750 was involved during a transportation. Of course, it would keep all the Events connected with all the pertinent Letters, but I only depicted a few hypothetical rows related to a supossed Letter Number 1750 in order to simplify the example. The sort of “chronological” data structure held in this table is commonly referred to as a time series.
The
Event.EventTypeCodecolumn, which should be constrained as a FK, holds values that indicate the kind of Event that was registered (in a user-friendly manner, in accordance with the points mentioned in the previous section).Application programs sharing data from tables representing the subtypes
A table with partial Event information (like the one previously deliberated) can be displayed in, e.g., a page of a web application program that provides a link that redirects to subsequent pages based on (a) the Event Type, (b) the Letter Number and (c) the Date and Time of the Event of interest.
In turn, the subsequent pages will exhibit the full Event information. E.g., if
EventTypeCodeis D,LetterNumberis 1750, andDateTimeis 2016-11-12 16:58:12.000,then the redirection must take to a page that presents the complete
Dispatchrow, e.g.:In case that
EventTypeCodeis T,LetterNumberis 1750, andDateTimeis 2016-11-15 10:12:05.000,the redirection ought to take to a page that sets out the pieces of information of the whole
Transportrow:Integrity, consistency and derivability
Evidently, a database structure with these characteristics entails a considerable set of constraints that must be put up to protect the integrity of the data, guaranteeing its consistency with the rules determined at the conceptual level (e.g., the cardinalities of the relationships between entity types/entity type instances).
Some of these constraints can be configured declaratively by way of PK and FK definitions, but others require resorting to a procedural approach —due to limitations in the declarative facilities provided by the major SQL database management systems—.
Among the rules that have to be enforced procedurally is the feature that (1) each
Eventrow must be suplemented at all times by (2) the respective counterpart in exactly one of the tables representing the subtypes, which (3) must “comply with” the value enclosed in theEvent.EventTypeCodecolumn —standing for the discriminator—, so the utilization of ACID TRANSACTIONS is convenient to ensure that these circumstances are always met. Other possibility would be employing TRIGGERS, but they tend to make things messy.And, yes, it would be practical to express and fix as VIEWs several queries that “combine” data from some or all the related tables in order to, e.g., simplify the pertinent data derivation.