Does a join table make the most sense?

[nycpost]

Hi everyone,

I have a database that tracks Takes from a film shoot. Each take has many different fields: scene #, take #, camera roll, starting TC, ending TC, duration, etc. etc. etc.

Each take can also be recorded on many different formats. For instance, a take can be shot on film (which is one format), transferred to videotape (which is another format) and then digitzed for editing (which is yet another format). For each take, I want to track what formats it lives on, so I'll know formats are available for viewing.

I have a Formats table in the database that lists all the characteristics of the different formats we're using: camera type, aspect ratio, resolution, frame rate, etc. Each format has about 30 distinct characteristics, so 30 fields per format. Each format can be used by many different takes.

What I'm trying to figure out is if I should include all the different format fields in the Takes table and make them lookups, or make a join table to house all of those fields since they're essentially all identical between the different formats. Does a many to many relationship like this sort of demand a join table?

What I like about having them all in the Takes table is that I don't need to script the addition of records to the join table. Data will be imported into the database with the different format key fields in place. I can't think of a way to get the import to work with the data organized that way (and I can't augment the import file). Also, when the data is all logged in the Takes records I can then tailor the format information if it changes from take to take: for instance if they shoot most takes at 24 fps, but then shoot a couple at 48 fps. I can make transactional changes. What are the drawbacks of having them in a join table? Any?

Any suggestions I could get would be great. Thanks,

Mike

[Vaughan]

When a take is recorded, does it go onto one or many different recording media?

You talk about a take having a start and end timecode: time codes are related to the media are they not? So if the STC and ETC field are in Takes table, how are the values for different media recorded?

[comment]

Does a many to many relationship like this sort of demand a join table?

Not necessarily - you could probably get by with a checkbox field in the Takes table. But if you also want to record something specific about the join (such as when was a specific take digitized, or by whom), then you do need a join table.

Under no circumstances you need to duplicate data from the Formats table into the Takes table.

[nycpost]

Vaughn and Comment, thanks for your replies.

When a take is recorded, does it go onto one or many different recording media?

It can. It depends on the workflow. On my current job, takes are recorded to a single "original" recording media. But the same camera we're using, which is digital, has the ability to simultaneously record both to external recording media (like HDCAM SR, in full bandwidth) and to Pro Res QTs on internal CF cards. So, I need the ability to support many different recording scenarios.

You talk about a take having a start and end timecode: time codes are related to the media are they not? So if the STC and ETC field are in Takes table, how are the values for different media recorded?

Not really. The temporal codes themselves have to do with the events recorded, the takes. The code format, however, (it's rate and type) is contingent on the format. For instance, when shooting 720p to XDCAM on some Sony cameras you can only shoot 50i fps or 59.94i fps, but if you shoot 720p to HDCAM SR on Viper you can also shoot at 23.98p, 24p or 25p frame rates. I track Code Type in the Formats table.

Under no circumstances you need to duplicate data from the Formats table into the Takes table.

But what if I need to modify the format data for that event? The format data generally remains constant, but sometimes characteristics do change, like frame rate or resolution. When shooting they may decide to change the settings on the camera for a shot - shooting at another frame rate, for instance, and this can affect the resolution, chromas subsampling, bit depth, etc.

Putting fields in the Takes table and then looking up the data from the Formats table gives me the power to change the data for a take if need be. It makes the data transactional.

But yes, this seems really inefficient, but maybe that's just what's necessary in this scenario?

Thanks,

Mike

[comment]

But what if I need to modify the format data for that event? The format data generally remains constant, but sometimes characteristics do change, like frame rate or resolution.

There are two ways to look at it: either this is a different format, or it's only a modification of an existing format.

In the first case, you need to create a new format (if the variant doesn't exist already) and assign it to the take.

In the latter case, you should record the modifications in the join table, since they only affect the specific take-format join. This is similar to an invoice where you lookup the product's price into the LineItems table, so it can be modified by the seller.

[nycpost]

In the latter case, you should record the modifications in the join table, since they only affect the specific take-format join. This is similar to an invoice where you lookup the product's price into the LineItems table, so it can be modified by the seller.

Yeah, these would be modifications, not new formats, so I think I need to go down the join table path.

If I need to export all the fields on a layout, and some of those fields are from a join table (or any related table), will the related fields also be exported? I seem to remember this being a limitation at one point.

Thanks,

Mike

[comment]

If I need to export all the fields on a layout, and some of those fields are from a join table (or any related table), will the related fields also be exported?

This is a loaded question... if you export from a join table, and include related fields from either parent table, they will be exported - the net effect of this is flattening the three related tables into one.

Exporting from a parent table with child fields included depends on the format; with most text formats you'll get something like:

Parent A    Child 1

            Child 2

            Child 3

Parent B    Child 4

            Child 5

[nycpost]

This is a loaded question... if you export from a join table, and include related fields from either parent table, they will be exported - the net effect of this is flattening the three related tables into one.

Exporting from a parent table with child fields included depends on the format; with most text formats you'll get something like:

I see.

What about exporting as XML from one of the parent tables? I would probably be exporting as XML and using an XSLT to transform it into an ALE.

If that wouldn't work, should I just create a new layout based off the join table that includes all the parent fields I would want to export?

Thanks,

Mike

[comment]

If you export as XML, then it's mostly a matter of convenience which table you choose as the source. The arrangement of the result is different for each - it's best you experiment with this and see for yourself.

You can export related fields (or local fields) without having them on the layout (you do, however, need a layout of the source table).

[nycpost]

Thanks, Comment. I'll play around with it and see how it works with XML. It's funny, I have join tables set up in other parts of my database, but for some reason in this particular instance it's been confusing me. I guess it's all about perspective. Thanks again for the guidance. Best, Mike

[comment]

Well, some would say it's not a join table, but a child table - and the Formats table is merely a template bank for the child record (since you lookup - and potentially modify - most if not all of the format fields). And you're probably not interested in grouping from the POV of Formats anyway (e.g. a portal showing all takes in 720p HDCAM) - so that's another point in favor of such view.