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

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’.
JoachimPetersenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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
JoachimPetersenAuthor Commented:
@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
ste5anSenior DeveloperCommented:
It depends on the requirements. What want you to do with it?
0
JoachimPetersenAuthor Commented:
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
ste5anSenior DeveloperCommented:
As I already wrote, when you don't need to query the detail information, then using XML is okay.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.