How to build this?

[dmontano]

We have "Product Photos"...literally 1000's of them. A product - identified by a unique part number - can have many photos of it. Some photos contain more than one product. A product can have many photos, a photo can have many products. So I would create a join table inbetween, as such:

product ----< product_photos >---- photos

Is that correct?

Next, we have "Product Illustrations"... literally thousands of these with the same parameters above (many-to-many) so I would model those as:

product ----< product_illustrations >---- illustrations

I have other "assets" (screenshots, charts, logos, etc.) that need to be related to a product. So I have this pattern repeatedly.

In this simple example, I think they could all be related as described in the first two examples. However, one of the primary reasons for building a "Digital Asset Management" system is to provide the User the ability to search for these "Assets" by typing in a part number and finding all available resources associated with that part number.

Now the problem, if I build each "type" of asset (photos, screenshots, charts, etc.) in the above scenario - I would end up with join tables that hold the information I want to search on. Is it possible to search across multiple join tables and deliver in one portal those results?

I do not think this is possible. So how do I solve this? The simple answer is to put all of these assets in one table - but I have struggled with this lumping together before and just find that it does not seem the way I want to go. I would have many fields empty from one type to another.

I really would like "both" - where I construct these tables as described above, and then "aggregate them somehow so a search in one field would deliver the results needed. I want to be able to search across ANY field - not just part number.

For example, I would like to see who the illustrator was for that illustration, who the photographer was for that photo, what date the chart was made, etc.

Any help is appreciated.

[mz123]

If all of your assets are images, you could have an "Image" field (container field) and then a"type" field where you enter text describing the image: chart, photo, illustration, etc.

Even though an illustrator is different than an author, they are nearly the same so you could have a conditional label, depending on the type.

case( type = "Illustration" ; "Illustrator" ; type = "Photo" ; "Photographer" ; "Creator" )

Or something of the sort.

Would that work?

[Fitch]

I agree with Martha: put all the assets in one table. If some asset types do have a lot of type-specific fields, you can build related tables for that data.

On the need for a join table: you are correct, something like

product ----< product_assets >---- assets

is generally the best approach (the other option is to go without the join table and use a multi-line key).

As for managing the images themselves, I'd highly recommend you take a look at SuperContainer.

[dmontano]

Hi Martha and Tom,

Sorry I had not even replied to say thanks. I have been all over the map on my "solution" and your input is helpful. I just have been hung up in other areas of "FileMaker".