Siim Viikman's blog

Now and then there comes a need to save some documents to the database also, along with the other information stored there already. It’s not a big deal to save some additional files to the database, especially when it’s not the main purpose of the application, just some extra. So no need for different storage mechanism.

This time I’m using NHibernate and I thought how I should structure tables and write mappings so that files data gets loaded only then when I really need it (eg. downloading a file). I also save some metadata about the file, some if it has business meaning, some of it doesn’t.

Database

I came up with the following simple schema:

I can load all the related meta-info about the file easily without touching the real content. That kind of schema also allows us to put FILE_DATA table to a different file group in MS SQL Server, so physically it can be located on other drive or partition whereas files metadata is located with our other tables.

Mappings

But this is only database side, there are also NHibernate mappings we have to work on. From NHibernate point of view, it’s not one-to-one relation but more like an one-to-many relation, meaning that File may have 0 or more FileData objects. So internally I mapped it like so, but to the outside I exposed it like one-to-one. I am using FluentNhibernate and here are my mappings for that:

public class FileMap : ClassMap<File>
{
	public FileMap()
	{
		DiscriminateSubClassesOnColumn("FILE_TYPE");

		Id(x => x.Id);
		Map(x => x.Type, "FILE_TYPE")
			.ReadOnly();
		Map(x => x.Name);
		Map(x => x.ContentType);

		HasMany<FileData>(Reveal.Member<File>("_files"))
			.Access.Field()
			.Cascade.AllDeleteOrphan()
			.Inverse();
	}
}

public class FileDataMap : ClassMap<FileData>
{
	public FileDataMap()
	{
		Id(x => x.Id);
		Map(x => x.Content);
		References(x => x.File);
	}
}

And related objects:

public abstract class File : IntegerIdentityEntity
{
	private IList<FileData> _files;

	protected File()
	{
		_files = new List<FileData>();
	}

	protected virtual IList<FileData> Files
	{
		get { return _files; }
	}

	protected virtual FileData FileData
	{
		get { return Files.SingleOrDefault(); }
	}

	public virtual string Type { get; protected set; }

	public virtual string Name { get; set; }

	public virtual string Number { get; set; }

	public virtual string ContentType { get; set; }

	public virtual byte[] Data
	{
		get
		{
			return FileData != null ? FileData.Content : null;
		}
		set
		{
			if (FileData != null && value == null)
			{
				FileData.File = null;
				Files.Remove(FileData);
			}
			else if(FileData != null)
			{
				FileData.Content = value;
			}
			else if(value != null)
			{
				var dataFile = new FileData {File = this, Content = value};
				Files.Add(dataFile);
			}
		}
	}
}

public class FileData : IntegerIdentityEntity,
{
	public virtual File File { get; set; }

	public virtual byte[] Content { get; set; }
}

As you can see, externally there is only one object – File, with property Data which returns the contents of the file as a byte array. Internally it is stored in the FileData collection because it’s the natural way how NHibernate handles such scenarios but that doesn’t mean we must always expose it as collection.

Conclusion

This approach allows to store files and their metadata in a database in a simple way, without adding any extra overload to fetching when it’s not necessary. From a database perspective it don’t add any extra constraints either, because we can put data table to a different partition so keeping the size of the main database reasonable.