Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shapper
Constraint Violating Yak Guru
450 Posts |
 Posted - 2012-11-26 : 20:49:21
|
| Hello,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,Miguel |
|
|
|
|
|
|
|
Table to Hold Data in a centralized way.