Best way to allow multiple one-to-many relationships

[Scott_Nesbitt]

I have one main table and about 30 sub-tables with one-to-many relationships.  Each record in the sub-tables can also have multiple photos. 

What is the best way to handle this?  I would rather not have another photos table for every sub-table, but I don't see another solution for this.

Thanks for any tips or pointers.

[graham_howe]

First ensure that every record in every table has a unique identifier. The easiest way to do this is have each of them with a key column (let's call it [ID]) with an initial value of uniqueid().

Then construct a single photo table which has not only the unique key column, but also another column to store the ID of the related record (we'll call that [relatedID].

Now if you are looking at record abcd1234 in table1, you will be able to access all related photos by filtering on [relatedID] = abcd1234.

[Scott_Nesbitt]

Right, that is how I was going to design the photo table (and how the existing 30 sub-tables work with the main table.)  The problem is that the relatedID would normally be a reference, but I cannot make it a reference to all 30 sub-tables.  So I don't see how this would work.

Perhaps I am missing something?

[Joseph_Seddik]

You can try this:

[Scott_Nesbitt]

Thanks for the idea!  I think I am very close, but not sure how to create the Photos virtual column in each Sub Table.  I tried a reference like below but that does not work.  And when I make the virtual column type List with this [Owner Id].[Related Photos] I just get the Photo Id, not the actual picture.  Could you please tell me the piece I am missing?

[Joseph_Seddik]

You should: 

1. Mark the "Photo" column in Photos table as Label, as shown in the diagram. 
2. Your virtual column should be of type List, and base type: Image.