Constraint Violating Yak Guru
Posted - 11/26/2012 : 20:49:21
On a SQL 2008 database I need a table to hold information as follows:
1 - Files (PDF, TXT, HTML, ZIP, PNG, MP4, ...)
2 - Text (Insert through a form in the application)
3 - HTML or other types of markup (Insert through a form in the application)
4 - VIDEOS (HTML 5 Video (1 to 3 files), You Tube Video (Markup)), ...
5 - IMAGES (PNG, GIF, ...)
6 - AUDIO (HTML 5 AUDIO (1 to 2 files).
So I ended up with some problems:
A - One item can have 3 files (An HTML 5 Video should have 3 files);
B - One type can be inserted in different ways (A TEXT can be inserted through a form or a file)
And when later, that item is edited, I need to be able to know how it was inserted to display the same UI.
C - One item can be local or from a online service:
Local Video: HTML 5 VIDEO with 3 files.
Remote Video: HTML Markup for HTML Video.
So I have a few types of content, different sources and different ways of being inserted (UI).
The approach I am planing is the following:
create table dbo.FilesPacks
Id int identity not null
constraint FilesPacks_Id_PK primary key clustered (Id),
Created datetime not null,
create table dbo.Files
Id int identity not null
constraint Files_Id_PK primary key clustered (Id),
PackOfFilesId int not null,
Data varbinary (max) filestream null
constraint Files_Data_DF default (0x),
[Key] uniqueidentifier rowguidcol not null
constraint Files_Key_U unique,
MimeAndInfo nvarchar (200) not null
) filestream_on [STORAGE];
So I have:
A file pack will allows me to create a Video with 3 Files or a PDF with one file.
B) Files - Using FileStream.
This allows me to hold small files, large files, text and html (in byte form), etc.
The data does not count to the database size.
As disadvantage I see one: Poor update performance.
But in fact the files, after being inserted, will be very rarely updated.
C) Files - Column MimeAndInfo
I have many types of sources and data as mentioned before.
- A VIDEO can be a file or HTML code for YouTube, Vimeo, etc.
- A Text can be a file or a text inserted from a form.
My idea is to hold that data in this column. Something like:
text/plain_f (Text inserted by f - form)
text/plain_u (Text inserted by u - uploading a file)
video/mp4_u (Video inserted by u - uploading file)
video/youtube_c (FAKE MIME: video from you tube using c - code)
So basically, this column will give me the MIME type and how was inserted into the database.
For a few contents which don't have a mime type I create fake mime types:
"video/youtube", "map/google" ... Of course this is internal to the application.
I have considered having many tables for classification but I am not sure ...
I wanted something flexible ... And that could be kind of controlled by the application.
NOTE: Each item also has an access level according to users ... but I left it out for sake of simplicity.
Well this is just a sketch but any advice is welcome ...