Solved

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

Posted on 2014-10-14
6
134 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 33

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 33

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 33

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Complex SQL script 1 31
Excel conversion issue with Sql server 14 46
user defined date datatype in SQL Server- can it be overdone.. 6 23
IIS Authorization for Web Service 2 21
These instructions are based on installing Owncloud on your new raspberry pi connected with a usb HDD. What do you need Part A? A Raspberry Pi, model B. A boot SD card for the Raspberry Pi. A usb HDD An Ethernet cable to connect to the lo…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

910 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

17 Experts available now in Live!

Get 1:1 Help Now