SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Table to Hold Data in a centralized way.
 New Topic  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  
 New 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.06 seconds. Powered By: Snitz Forums 2000