Search code examples
mysqldatabase-normalization

MySQL Does having two routes connecting information between two tables violate normalization rules?

Not sure I know the right terminology, but is there anything wrong / invalid about having converging relationships?

The things I'll be querying frequently:

  1. Which cameras were at event
  2. Which photos came from event
  3. Which photos came from camera

For #3, I'd prefer to avoid the double-join through event if it is not wrong for me to also connect the image DIRECTLY to the camera table. I also can't combine these tables, as there is more info in the real-world application.

  1. Does this violate normalization?
  2. Is there a more optimal way to organize this info?

Note: the "event" table might be better called a cam_event table, joining a not seen event info table (1-n) and a camera info (1-n).

enter image description here

Solution

  • Normalization We begin design by defining predicates (statement templates parameterized by columns) and tables (each holding the rows that make a true statement from its predicate) sufficient to describe all business situations that can arise according to the business rules. Then we normalize. Information modeling methods intentionally produce predicates/tables with somewhat normalized designs.

    We can only generally address your question(s) since you haven't given enough information about your design: relevant predicates & tables and either functional dependencies (FDs), candidate keys (CKs), join dependencies (JDs), foreign keys (FKs) & other constraints restricting database states or rules restricting business situations. You should learn and use some information modeling method and normalization procedures to 5NF. (From which one might want to denormalize to a lower NF.)

    FK Graphs FK "routes connecting information between two tables" & "convergence" (whatever you mean by or heard about them) are irrelevant. A FK tells the DBMS to enforce that certain subrows of a table must appear in as certain other subrows as a consequence of the predicates & business rules. Typical DBMSs needlessly poorly support directed FK cycles, but that is irrelevant to finding the best initial design.

    Photos From your question, a table you likely want is:

    -- photo PHOTO was taken by camera CAMERA at event EVENT
Shot(photo, camera, event)

    Presumably this has one CK/PK {photo} per the FDs that presumably hold in it.

    Cameras/Events To record that a camera has certain intrinsic properties (the analysis for events is similar) you could add:

    -- camera CAMERA is a model MODEL and ...
CameraEtc(camera, model, ...)

    Otherwise to record cameras regardless of whether they have taken photos at an event you could add:

    -- CAMERA identifies a camera
Camera(camera)

    Presumably camera values in Photo would have to appear in the camera column of the table you add. So there would be a FK from Photo {camera} referencing it.

    But if your only cameras are ones for which some photo has been taken at some event then select * from Camera = select camera from Shot so you wouldn't need Camera.

    Camera-Event Assignments To record that a camera was assigned for use at an event regardless of whether some photo was taken by it at that event you could add:

    -- camera CAMERA is assigned for use at event EVENT
Assigned(camera, event)

    Given Assigned, presumably the camera & event of a shot have to be assigned. So a Shot camera-event pair must appear in Assigned. So Shot has a FK {camera, event} referencing Assigned.

    An alternative to Shot is:

    -- photo PHOTO was taken by camera CAMERA
ShotCamera(photo, camera)
-- photo PHOTO was taken at event EVENT
ShotEvent(photo, event)

    because select * from Shot = select * from ShotCamera natural join ShotEvent. But the corresponding constraint says that the camera & event for a photo must be assigned, ie that the natural join of ShotCamera & ShotEvent has a FK referencing {camera, event} in Assigned, ie what the Shot FK says. Since these table(s) and (especially in SQL) constraint(s) are both more complex, we prefer the Shot design.

    Augmenting Predicates/Tables To record properties of assignments you could have an augmented Assigned with additional parameters/columns. This is because such a property would be single-valued per assignment, so such a column would be single-valued per camera-event pair, so {camera, event} is also a CK of the augmented table, and it happens that the normalization process would not suggest decomposing the augmented table.

    The same goes for augmenting Photo, with the CK being {photo}.

    But to record properties of cameras regardless of whether they are assigned you should instead have an augmented Camera for additional parameters/columns. Because an augmented Assigned is problematic:

    -- camera CAMERA is a model MODEL and ... and is assigned for use at event EVENT
AssignedEtc(camera, model, ..., event)

    It is always the case that select * from AssignedEtc = select * from CameraEtc natural join Assigned. This is because the natural join of tables holds the rows satisfying the conjunction of their predicates. Ie AssignedEtc's predicate is CameraEtc's predicate ANDed with Assigned's predicate: "camera CAMERA is a model MODEL and ... and camera CAMERA is assigned for use at event EVENT". But the AssignedEtc design has certain disadvantages compared to the CameraEtc+Assigned design addressed by normalization: Every time a given camera appears it has to be with the same model, ... values. Whereas normalizing the AssignedEtc design leads to a design like CameraEtc+Assigned.