Database design problem - keeping relationships at a minimum

Question

I have a database design that's normalized well enough, but now I need to add a new table (entity, since I'm actually using Core Data) and I'm running into some issues.

Landscape has many Assessment
Assessment has one AssessmentType
AssessmentTree has one Assessment
AssessmentTree has one each of 12 other entities, omitted for space.

Now, I'm adding a Photo entity, which can be associated with ANY of the above entities. I need to have some sort of relationship in place such that I can grab only the Photos related to an AssessmentTree, for example, or perhaps an Assessment, which would be a superset of the Photos related to an AssessmentTree.

(I feel I may not have done a great job explaining the situation so let me know if I can clarify in some way.)

Possible solutions I've considered, none of which are satisfying:

1. Create references to every other object from within the Photo entity. Nil references would then mean the Photo was not a part of that set.
2. Create a new entity, PhotoRelations, that would hold a reference to the Photo and a reference to one of the objects it's attached. The problem is that while I could easily do this with an SQL system, I can't think of how to translate it into CoreData.

Any help would be appreciated!

Answer 1

What you could do is create a relation table like this:

linkedId | photoId | LinkType

LinkType can be any of the above listed as an integer 1-N

Answer 2

Since Photo is a child then I would go with your first option and create a link to each entity.

However, what do you plan on storing in Photo? If it is just the binary data then I would reconsider it and store a filePath to the image on disk instead. Storing binary data in Core Data is less than ideal. Then if you get down to just storing the file path you may be able to skip the entity entirely and just store the filePath in the parent object(s).

Answer 3

The way that Core Data is designed to handle this problem is through entity inheritance.

In theory, you would handle this just the way you would with non-Core Data objects defined with subclasses. You would define an abstract parent entity that links to a concrete photo entity. Then you would have all the entities that need a photo inherit from that parent entity.

However, owing to an implementation detail with SQL stores, all the child entities of a parent entity in any graph end up in the same SQL table on disk. This can degrade fetch performance because all the instance of all sub-entities stored in a table have to be searched even if your just fetching one entity.

It's a pain. I keep hoping they will refractor that limitation out.

You could test the entity inheritance with test data and see if it is a problem or not for your particular implementation. If you have only a few thousand sub entities that fetch on simple attributes (strings, numbers, dates etc), then you may be able to use entity inheritance without any significant performance hit.