Solved

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

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

786 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