Solved

What is the best way of constructing the tables for this? (see post)

Posted on 2014-10-14
6
131 Views
Last Modified: 2014-11-04
I have different kind of medias, video, picture, audio.
They all need diffrent kind of information about them, here is a data tree to represent what the diffrents of each media has:

I have different kind of Medias; video, picture, audio.
They all need different kind of information about them; here is a data tree to represent what the difference of each media:
•      Picture (if not thumbnail)
      o      ImageId
      o      ThumbnailId
      o      ImageFormat
      o      ImageWidth
      o      ImageHeight
      o      ImageBase64Bytes
      o      CreatedDate
•      Picture (if thumbnail)
      o      ImageId
      o      ImageFormat
      o      ImageWidth
      o      ImageHeight
      o      ImageBase64Bytes
      o      CreatedDate
•      Video
      o      VideoId
      o      ThumbnailId
      o      VideoFormat
      o      VideoWidth
      o      VideoHeight
      o      VideoDuration
      o      VideoBase64Bytes
      o      CreatedDate
•      Audio
      o      AudioId
      o      AudioFormat
      o      AudioDuration
      o      AudioBase64Bytes
      o      CreatedDate
Above described 4 separate tables to store each media type, but I find that kind of messy and unprofessional.
So you see they all have different kind of fields, a second way I thought to store them was to make a table like this:
•      Media (hold all media types described above)
      o      MediaId
      o      MediaType (0,1,2 - depending on if it is a picture, video or audio)
      o      MediaInformation (all the in information that differs from each media type) - XML
      o      MediaBase64Bytes
      o      CreatedDate

Do you guys have any other solution on how to construct the table/s to contain these infomations??

Note: There will of cause be a table to contain information of who uploaded it the media and so on, but not in the 'file database/media database' itself.

Thank you - I am not very experienced in the database construction area and want it done ‘right’ and not just ‘working’.
0
Comment
Question by:JoachimPetersen
  • 3
  • 2
6 Comments
 
LVL 32

Expert Comment

by:ste5an
ID: 40379702
The XML approach is a possibilty.

But it depends on the requirements. Can we assume that the details form a compound value, which never need to be handled in T-SQL?

What kind of entity represent your media? A library?

btw, this is a correctly normalized model:

CREATE TABLE Media
    (
      MediaId INT ,
      MediaType INT CHECK ( MediaType IN ( 0, 1, 2, 3 ) ) ,
      CreatedDate DATETIME ,
      CONSTRAINT PK_Media PRIMARY KEY ( MediaID ) ,
      CONSTRAINT UQ_Media UNIQUE ( MediaID, MediaType )
    );

CREATE TABLE Picture
    (
      MediaId INT ,
      MediaType INT CHECK ( MediaType = 0 ) ,
      ImageWidth INT ,
      ImageHeight INT ,
      ImageBase64Bytes NVARCHAR(MAX) CONSTRAINT PK_Picture PRIMARY KEY ( MediaID ) ,
      CONSTRAINT FK_Picture_MediaID FOREIGN KEY ( MediaID, MediaType ) REFERENCES Media ( MediaID, MediaType )
    );

CREATE TABLE Audio
    (
      MediaId INT ,
      MediaType INT CHECK ( MediaType = 1 ) ,
      AudioDuration INT ,
      AudioBase64Bytes NVARCHAR(MAX) CONSTRAINT PK_Audio PRIMARY KEY ( MediaID ) ,
      CONSTRAINT FK_Audio_MediaID FOREIGN KEY ( MediaID, MediaType ) REFERENCES Media ( MediaID, MediaType )
    );    

Open in new window

0
 

Author Comment

by:JoachimPetersen
ID: 40379842
@ste5an
The medias are from people. Like an image from you mobile phone being uploaded or a video recording from your camera.
But what is the best way to store these diffrent type of medias, in regards to 'table construction'? remember preview images for images and videos.
0
 
LVL 32

Expert Comment

by:ste5an
ID: 40380052
It depends on the requirements. What want you to do with it?
0
 

Author Comment

by:JoachimPetersen
ID: 40380156
I simply need it in the database so I can display it in a website/app(if the persons have permission to see it), so I need all the information like width, height, duration, thumbnail, etc. (depending on what media it is)
(base64 because I use XML web service)
0
 
LVL 32

Accepted Solution

by:
ste5an earned 500 total points
ID: 40381632
As I already wrote, when you don't need to query the detail information, then using XML is okay.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API (http://dictionary.reference.com/browse/API?s=t) has made its way into the popular lexicon of the English language.  A few years ago, …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The purpose of this video is to demonstrate how to set up the WordPress backend so that each page automatically generates a Mailchimp signup form in the sidebar. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now