I’ll use C# to design the model to be persisted
Let’s suppose I have the following entity:
public class Episode {
public int Id { get; set; }
public string Title { get; set; }
public string Image { get; set; }
}
This a very simple entity, which is easy to map to a relational model.
But, what about if I want my Episode entity to have one or more images?
How would I represent this?
I can, for example create this entity model:
public class Episode {
public int Id { get; set; }
public string Title { get; set; }
public ICollection<string> Images { get; set; }
}
But this, does not assure I have at least one image in my collection.
Furthermore, how would I design the relational model for this entity? How can I represent an entity that has a “1 to many” relationship with a string?
Do I need to create an additional table just for storing these “strings”?
I would really appreciate any advice you may want to share, and the best approach to this problem
First you have to decide if it is sufficient to have runtime safety about the number of images or if you want to have compiletime safety.
Runtime safety
Achieving runtime safety is quite easy. All you have to do is remove the default setter for Images and implement a setter that performs a check before saving the given ICollection<string> argument regarding if the collection has at least one element. Also make sure, that there is no way to change Images other than using this safe setter.
The drawback is of course, that one can try to call the setter with an emtpy list and your IDE won’t show you that it will fail at runtime.
Compiletime safety
To get compiletime safety you need to spend more effort. As you recognized, a collection makes it able to store no items in it. That belongs to its contract and that is totally fine. However in your case, you don’t want that. Therefore, the a collection is not the correct type and you need to create you own type. My best idea is to create a class that implements the list specific features and delegates it to an internally saved list. However, the methods that could set the internal list empty, have to be intercepted to make sure the list is never empty. In the constructor you then have to do something messy. It has 2 parameters: T Head and ICollection<T> Rest (both must be not null). The Head and all items of the Rest are then put into the interal list. When the first argument T is not null then the interal list is not empty for sure and as you intercept all the methods that change the list, you can make sure it will never become an empty list.
It is not very elegant in my opinion because you can’t create this type just from a list. But in exchange you get compiletime safety, so everyone trying to instanciate this datatype empty, will have the IDE complaining because it is not possible.
About your additional question for storing it in the database: Yes, I think the only way is to have an additional table for the image-strings because it is “1 to many”. There is no way around this unless you give up the normalisation.
In the code:
- Validate in the setter the collection has at least one item, and raise and exception if it doesn’t.
In the database:
- Create an
episode_imagetable. That table would have a foreign key with theimagetable. - Unfortunately, there’s no way to enforce that for every row in the parent table, there’s at least one corresponding row in the child table. Only the other way around is possible. Imagine this: if foreign keys worked both ways, it’d be impossible to insert a row in A because corresponding row in B doesn’t exist, but you couldn’t insert the needed row in B either, because no corresponding row exists in A. That kind of constraints are possible in a conceptual model but not in a physical database.
There are a few options:
-
Just use a regular
ICollection, document the fact that it must be non-empty, and enforce this in the setter. In the database, you would have aepisode_imagestable. This doesn’t provide the compile-time safety you’re seeking, but in my opinion it’s the only reasonable option. -
You could store a
FirstImagefield and aOtherImagesfield. In the database, you could have afirst_imagecolumn inepisodes, and aother_episode_imagestable. I wouldn’t suggest this approach as it would be awkward to work with that API. -
As @valenterry said, you could introduce a new non-empty collection type, which could store
FirstElementandOtherElementsfields. I wouldn’t suggest this as callers wouldn’t like being forced to work with a non-standard collection type.
Do I need to create an additional table just for storing these “strings”?
If you really don’t want an additional table, you could store serialized collections in a text column, like ["image1", "image2", "image3"]. But a separate table is more idiomatic, and it works better if you need to do some analysis from a SQL console, such as finding the episode with the most images.