Don't truly know how to name this question.
But whats the best way (regarding database design) for storing images for different purposes?
Let me give an example.
I have a bunch of user photos and i got another 5 different sets of photos (like user photos but with no connection to user photos).
Is the best thing to store all photos in a single database table and try to reference them from within that table?
or is the best to create different tables for each set of photos?
I can see one benefit from creating multiple tables and that's the cascade delete function for removing the photo when the main object is deleted.
Any other aspects to consider?
Another example could be addresses. A user can have an address but so can a company or a location.
Create one table for all addresses and try to have some sort of index tables to reference what address belongs to what object or have different tables and eliminate the problem.
Sorry again for the bad naming of the question.
How to store large blobs in sql server
Storing large chunks of binary data in SQL Server is not a great approach. It makes your database very bulky to backup and performance is generally not great. Storing files is usually done on the file system. Sql Server 2008 has out of the box support for FILESTREAM.
Microsoft documents the cases to use FileStream as follows
- Objects that are being stored are, on average, larger than 1 MB.
- Fast read access is important.
- You are developing applications that use a middle tier for application logic.
In your case I think all points are valid.
Enable on Server
To enable FILESTREAM support on the server use the following statement.
EXEC sp_configure filestream_access_level, 2
RECONFIGURE
Configure the Database
To get a filestream filegroup linked to your database create
ALTER DATABASE ImageDB ADD FILEGROUP ImageGroup CONTAINS FILESTREAM
ALTER DATABASE ImageDB
ADD FILE ( NAME = 'ImageStream', FILENAME = 'C:\Data\Images\ImageStream.ndf')
TO FILEGROUP TodaysPhotoShoot
Creating the table
The next step is getting your data in the database with filestream storage:
CREATE TABLE Images
(
[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL PRIMARY KEY,
[CreationDate] DATETIME NOT NULL,
[ImageFile] VARBINARY(MAX) FILESTREAM NULL
)
For Filestream to work you not only need the FILESTREAM property on a field in the table, but also a field which has the ROWGUIDCOL property.
Inserting Data with TSQL
Now to insert data in this table you can use TSQL:
using(var conn = new SqlConnection(connString))
using(var cmd = new SqlCommand("INSERT INTO Images VALUES (@id, @date, cast(@image as varbinary(max))", conn))
{
cmd.Parameters.AddRange(new {
new SqlParameter("id", SqlDbType.UniqueIdentifier).Value = uId,
new SqlParameter("date", SqlDbType.DateTime).Value = creationDate,
new SqlParameter("image", SqlDbType.varbinary).Value = imageFile,
});
conn.Open
cmd.ExecuteScalar();
}
Inserting data using SqlFileStream
There also exists an approach to get the file data on disk using Win32 directly. This offers you streaming access SqlFileStream inherits from IO.Stream.
Inserting data using win32 can be done with for example the code below:
public void InsertImage(string connString, Guid uId, DateTime creationDate, byte[] fileContent)
{
using (var conn = new SqlConnection(connString))
using (var cmd = new SqlCommand(@"INSERT INTO Images VALUES (@id, @date, cast(@image as varbinary(max)) output INSERTED.Image.PathName()" , conn))
{
conn.Open();
using (var transaction = conn.BeginTransaction())
{
cmd.Transaction = transaction;
cmd.Parameters.AddRange(
new[] {
new SqlParameter("id", SqlDbType.UniqueIdentifier).Value = uId,
new SqlParameter("date", SqlDbType.DateTime).Value = creationDate,
new SqlParameter("image", SqlDbType.VarBinary).Value = null
}
);
var path = (string)cmd.ExecuteScalar();
cmd.CommandText = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()";
var context = (byte[])cmd.ExecuteScalar();
using (var stream = new SqlFileStream(path, context, FileAccess.ReadWrite))
{
stream.Write(fileContent, 0, fileContent.Length);
}
transaction.Commit();
}
}
How to model a Photo storage database
With the filestream approach to store the images the table is very narrow which is good for performance since many records can be stored per 8K data page. I would use the following model:
CREATE TABLE Images
(
Id uniqueidentifier ROWGUIDCOL NOT NULL PRIMARY KEY,
ImageSet INTEGER NOT NULL
REFERENCES ImageSets,
ImageFile VARBINARY(MAX) FILESTREAM NULL
)
CREATE TABLE ImageSets
(
ImageSet INTEGER NOT NULL PRIMARY KEY,
SetName nvarchar(500) NOT NULL,
Author INTEGER NOT NULL
REFERENCES Users(USerId)
)
CREATE TABLE Users
(
UserId integer not null primary key,
UserName nvarchar(500),
AddressId integer not null
REFERENCES Addresses
)
CREATE TABLE Organsations
(
OrganisationId integer not null primary key
OrganisationName nvarchar(500),
AddressId integer not null
REFERENCES Addresses
)
CREATE TABLE Addresses
(
AddressId integer not null primary key,
Type nvarchar(10),
Street nvarchar(500),
ZipCode nvarchar(50),
City nvarchar(500),
)
CREATE TABLE OrganisationMembers
(
OrganisationId integer not null
REFERENCES Organisations,
UserId integer not null
REFERENCES Users,
PRIMARY KEY (UserId, OrganisationId)
)
CREATE NONCLUSTERED INDEX ixOrganisationMembers on OrganisationMembers(OrganisationId)
This translates to the following Entity RelationShip Diagram:
- Performance wise, the narrow images table is very good as it contains only a few bytes of data per record.
- We can assume that an image is always member of an Image Set, The Set information could be hidden if there is only 1 image in it.
- I assume you want to track which users are member of which organisations, so I added a table to link them (Assuming a user can be member of multiple organisations).
- The primary key on the OrganisationMembers table has UserId as first field since there normally a lot more users than Organisations and you probably will want to show which organisations a user is member off more often than the inverse.
- The index on OrganisationId in OrganisationMembers is there to cater for queries where the list of members for a specific Organisation needs to be shown.
References:
EDIT 5: removed table definition - turned out to be not relevant in the end