9 Replies Latest reply on Aug 28, 2009 5:06 PM by comment_1

    easy one (i hope) TABLE relationships and making records with portal entries read as unique--read on

    ijontichy

      Title

      easy one (i hope) TABLE relationships and making records with portal entries read as unique--read on

      Post

      Okay, I'm working on a film and I'm keeping a code book.   This is a record of EVERY SINGLE SHOT captured by the camera.

       

      Concurrent with this, VFX staff is keeping a database of just the VFX shots from the film.

       

       

       

      Now in MY database, every take is its own record.   77A tk 1, 77A tk 2, 77A tk 3, etc.

       

      In THEIR database, each record is a specific SET UP for a shot, but the individual takes are portaled in from another table.   So even though they reference the set up (77A) they are not referencing the take except via a portal. 

       

      I can get the set up info to correspond to my set ups, but I'd also like to harvest data they've recorded that's specific to every take.   

       

      And while I've tried to use the tables, I'm having no love.    Some suggestions would help this foggy head.   Thanks in advance!

        • 1. Re: easy one (i hope) TABLE relationships and making records with portal entries read as unique--read on
          philmodjunk
            

          Sounds like their Portal records have two fields with one field identifying the shot (77A) and another that identifies the "take" (tk 1, tk 2...)

           

          I think you have two choices:

           

          1. In their table, define a calculation field that combines these two values and relate to it.
          2. In your table, define two calculations that split your key (77A tk 1) -> key 1 = 77A, key 2 = tk 1 and then relate both of these fields to the matching pair of fields in their table.

           

          Either approach should work.

          • 2. Re: easy one (i hope) TABLE relationships and making records with portal entries read as unique--read on
            comment_1
              

            Can you explain what you mean by:

             


            pirxx wrote:
            I'd also like to harvest data they've recorded that's specific to every take.  

            BTW, if you ask me, their setup of 'one shot has many takes' makes more sense. Your way, you need to duplicate shot attributes in every take record of the same shot.

             

            • 3. Re: easy one (i hope) TABLE relationships and making records with portal entries read as unique--read on
              ijontichy
                

              Each person is using the dominance that makes sense for what they do.   In editorial, each shot has to be its own record, as it relates to one specific roll of the camera with a specific negative keycode address, etc.    So discrete pieces of media are what is needed.   The Shot is king.

               

              But for VFX, the setup is king.   And there are lots of takes with identical data.   But sadly, it's not always identical.   And being VFX I have to note the differences.

               

              Let me try and describe this better because I don't think I decribed this very well.

               

              In my database, in the tabled called [CODEBOOK] I have unique fields for

              Slate (77a)

              Take #  (1, 2, 3, etc)

              And a bunch of other data unique to each rolling of the camera.

               

              In their database in one table [SET DATA] they have

              Slate (77a)

              And a bunch of other data unique to the VFX needs of that set up.

              Then portaled into that table (from another table called [TAKES] via a relationship of a random# not specific to anything), they bring in the takes and other data that they've chosen to record in the Takes table.

               

              Now, I can pull in the SET DATA info by creating a table relationship using Slate  to connect the CODEBOOK and SET DATA tables.

               

              But I can't figure out how to pull in the unique Take information into each take in my database.

               

               

               

              Furthering the issue, though not related to my current problem, is that they are grouping information informally in their takes table.

               

              Example-- they might enter "1-3" in the takes field since nothing changed for those 3 takes.   Human readable, yes, but when I'm turning over VFX shots for work and I've picked take 2 I don't CARE about takes 1 and 3.    I'm prepared that I'm going to have to create more records in their takes table to handle this, duplicating several repetitive values.   No biggy.

               

              I hope that makes more sense.

               

               

               

               

              • 4. Re: easy one (i hope) TABLE relationships and making records with portal entries read as unique--read on
                philmodjunk
                  

                "...Then portaled into that table (from another table called [TAKES] via a relationship of a random# not specific to anything),... "

                That doesn't make much sense. I assume from a given Setup, their portal shows all the "takes" for that set up. If so, I suspect there's a field in takes that shows the slate (77a) number. That should be what you need.

                • 5. Re: easy one (i hope) TABLE relationships and making records with portal entries read as unique--read on
                  ijontichy
                    

                  Surprisingly there is not.    Instead they set up a random ID and then named it differently in [Takes] and [Set Data].   That's the correlating field.

                   

                  But visualizing tables for a second, Having related the "slate" field between  [Codebook] and [Set Data], [Takes] is still floating off on its own.

                   

                  Are you saying that I should create a calculation in [Takes] that gives me [Set Data]'s "slate" field and connect the tables that way instead?

                   

                  Cheers,

                   

                  e.

                  • 6. Re: easy one (i hope) TABLE relationships and making records with portal entries read as unique--read on
                    philmodjunk
                      

                    Let's clarify a key point here: What's the relationship between Takes and SetData that is used by the portal you mentioned?

                     

                    I was imagining SetData::Slate = Takes::Slate

                     

                    If there's a serial number field in SetData that's being used, that's actually a much better design. Perhaps you should import that serial number and use it as your key on your side of the fence as well.

                    • 7. Re: easy one (i hope) TABLE relationships and making records with portal entries read as unique--read on
                      ijontichy
                        

                      It is not.  

                       

                      in [SetData] that field is called "setupID"    In [Takes] that field is called "takeID"

                       

                      And if you have a moment, what makes this superior from a database design point of view?

                       

                      I'd rather not link anything to it because it's so arbitrary, but I'll happily learn new philosophy that makes my life better.  

                       

                      But in essence I need to be connecting [Codebook] via [Takes] rather than [Set_Data] if I'm interpreting you correctly.   (and then I have to make sure each take is represented in [Takes] or the record won't come into my codebook.

                      • 8. Re: easy one (i hope) TABLE relationships and making records with portal entries read as unique--read on
                        philmodjunk
                          

                        So the relationship is SetData :: SetupID = Takes :: takeID, where SetData is a serial number field?

                         

                        The arbitrary nature of this Id code is what makes it a better design for using as a primary key. The generation of the serial ID is dirt simple and easy to enforce as a unique primary key. Other options require considerable more programming support with no added value to using them in a relationship. If you need other fields to label the records such as your slate ID, you can keep that as a separate field and use it in searches and sorts, but not as a key field in a relationship.

                         

                        If I've read these posts correctly and am building the correct mental model of the tables. SetData has two fields of interest: SetupID and the "Slate" number. If you import the SetupID values into a new field in your Codebood table ( you could use the slate ID field as your "matching" value perhaps), you could use it to relate Codebook to Takes.

                         

                        Well, it's after 5 and I got get started on my weekend. Perhaps others will help you out over the weekend. I'll try to check back here on Monday.

                        • 9. Re: easy one (i hope) TABLE relationships and making records with portal entries read as unique--read on
                          comment_1
                            

                          Try defining a calculation field in your CODEBOOK table =

                          SET DATA::SetID


                          where SetID is the "random# not specific to anything" field used as the matchfield to TAKES.

                          Then define the relationship from CODEBOOK to TAKES as matching both the calculation field and the take.


                          pirxx wrote: 

                          they are grouping information informally in their takes table.


                           

                           

                          It depends of how consistent their data entry is. In your example, you could take "1 - 3" and turn it into "1¶2¶3" using a custom function or a repeating calculation field. This would make their one take records match all three of your take records.




                          BTW, I am not sure you follow my earlier remark. You have a table of takes, and so do they. The difference is that they also have a parent table for each group of takes - and you don't. Their structure is normalized, and yours isn't. You have no place to enter information that is specific to a group of takes (identified by a common Slate value), and enter it only once.