0

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

Trees4theForest
  • 1,267
  • 2
  • 18
  • 48
  • 2
    Consider [Junction Tables](http://stackoverflow.com/a/32620163), highly performant, normalized. – Drew Jul 09 '16 at 05:11
  • @drew Not sure I get how a junction table would work here. I know them to be the mediary for a n-n relationship, but these are all 1-n connections. – Trees4theForest Jul 09 '16 at 06:50
  • What's the problem with the double join? That said, if your method works for you, then run with it, but I don't think that it's what I'd do. – Strawberry Jul 09 '16 at 06:53
  • The never-ending fear I'm doing things inefficiently, or wrong ;).. and an irrational desire to have as much info available everywhere. Would you then just remove the camera-photo link? – Trees4theForest Jul 09 '16 at 06:56
  • 1
    why is `date` an `int` and `cameras_idcamera` a `date`? Shouldn't that be the other way around? Also, this assumes that there would only be one camera per event. Isn't Cameras to Events a many to many relationship? Couldn't you have 2 cameras at the same event? Seems like you need a bridge table here. – starshine531 Jul 09 '16 at 07:06
  • Pay no attention to the types -- this is example. (ie, assume all data types are correct and normal) – Trees4theForest Jul 09 '16 at 07:20
  • Ahh --- good point... camera - event should be n-n... maybe that's partly why I'm chasing my tail. – Trees4theForest Jul 09 '16 at 07:21
  • Ok, then association tables if not junction tables. Same concept. Plop out a schema if you want (not an ERD). But whatever works and is fast and sane, go with it – Drew Jul 09 '16 at 09:01
  • 1
    Your diagram is unclear. Please give & explain all relevant parts of your design, since we can't evaluate when parts are "not seen". Ie tell us, for any row, when it goes in a table, since a design is otherwise unusable; when there are "converging relationships", since otherwise we can't tell you how you are mistaken, since there is no "convergence" problem; and, as much as you can, give either CKs, FKs, JDs & other constraints restricting database states or rules restricting business situations. PS Also your drawing's product, since different ones use different symbols. – philipxy Jul 10 '16 at 03:13
  • Since cameras_idcamera isn't part of the event PK there can only be one camera per event--is that what you want? Is the PK of event (idevent, location)? If so then why isn't location in photos? – philipxy Jul 11 '16 at 14:59

1 Answers1

2

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.

philipxy
  • 14,867
  • 6
  • 39
  • 83