Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Table to Hold Data in a centralized way.
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Constraint Violating Yak Guru

450 Posts

Posted - 11/26/2012 :  20:49:21  Show Profile  Reply with Quote

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) FilesPacks.

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 ...

Thank You,

  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000