Search code examples
databasedatabase-designrelational-databaseerd

Does this diagram have redundancy?

I have question about data redundancy.

I have following diagram: enter image description here

Packages are stored in Warehouses. A package can be moved from one warehouse to another. I want to store information about where a package was on certain date (in which warehouse) and information about transports between warehouses. Transport departs from one warehouse with multiple packages and arrives at different warehouse. Package can be in certain warehouse only once (if it is transported to another warehouse, it cannot, at any time, go back to previous warehouse.)

I came up with this:

enter image description here

Is this second diagram correct or is there redundancy?

Edit:

I changed Deliveries to Packages as my intention was that Delivery is one undividable thing (like a box or letter) - sorry for confusion, it was a bad name. So based on @philipxy answer, I think this diagram is better for what I want to achieve. I removed DateStorage, and add inital warehouse property to Packages.

enter image description here

Solution

  • You seem to be using "delivery" in two different ways: some stuff, and a part of that stuff initially (or most recently?) stored at a particular warehouse (associated with just one date?). Then a part (each identifiable by an initial storage delivery-warehouse pair?) might have been transported from where it was to a different warehouse any number of times. Can a delivery then have two parts in the same warehouse after transpord? Can a part be transported twice on the same day? Can a transport be to the same warehouse?

    Your question's characterization of your application's tables/relationships and constraints is unclear; but it seems you might need an answer that suggests how you can proceed before it can become clear.

    You seem to be interested in something like the following. (I don't claim it's consistent with everything in your question; but neither is everything in your question.) Using ER, for every base entity type & relationship type of interest we give a predicate determining its table's rows--a fill-in-the-blanks sentence template parameterized by column names.

    -- DID ids a delivery
Delivery(did)

-- WID ids a warehouse
Warehouse(wid)

-- a delivery DID part was initially stored at warehouse WID on date D
storage(did,wid,d)
    key {did, wid}
    FK (did) to delivery, (wid) to warehouse

-- part DID-WID was transported from warehouse FID to warehouse TID on date D
Transport(did, wid, fid, tid, d)
    key {did, wid, fid, d}, {did, wid, tid, d}
    FK (did, wid) to storage, (fid) & (tid) to warehouse

    This may or may not capture state and constraints of your application situations.

    However, supposing this is consistent with your application, it isn't necessarily part of the design you want. (Although you might be able to express the design you want in terms of it.)

    Eg If a part can be transported only once per date then you don't need date d in the Transport keys. Eg You could drop fid from Transport, because you could reconstruct it from its did & its chain of transports. Eg If you only care about where parts are now, you don't want this Storage. Eg You may want initial storage, transports, and where a part is now (essenially, a part entity table), even though you can derive the latter from the former.

    Eg If a delivery can only have one part per warehouse then you can also identify a part by its current warehouse, so you could have:

    -- same predicate as Transport
-- part DID-WID was transported from warehouse FID to warehouse TID on date D
Transport2(did, fid, tid, d)
    key {did, fid, d}, {did, tid, d}
    FK (did) to delivery, (fid) & (tid) to warehouse

    In either design a part's delivery plus the warehouse at the start of the chain of its transports must form a pair in storage.