Solved

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

Posted on 2014-10-14
6
147 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 34

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 34

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 34

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In order to have all security and back ups taken care of, WordPress users can sign up for services with WP Engine.
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…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

628 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