Table Design for (Currently Viewing Videos)?

I am doing a project on video portal. In that I am trying to build a place for currently viewing videos. To hold people who are currently viewing that video I have designed a table like this:

table:(columns)

Sno

videoid

sessionid

userid

createddate

These are the columns but it is not sufficient I think. If possible can you help me? How c开发者_StackOverflow社区an I design the table? How can we perfectly represent the currently viewing videos?

Please help me. Thank you.

Your design looks fine. I would add two fields:

beginView : DATETIME (not NULL)
endView : DATETIME 

When you start to stream a video, you can add a new row into the table with the userid, sessionid, videoid, beginView time, and the time the video was opened (assume current time, unless you have other information available.) I'm assuming the Sno is an identity column, which you get from the call to insert the row. This ID is stored in the session. When the session is closed, any open records can be closed by updating the endView value - they are no longer "current" viewings.

To find the rows that are current, use the WHERE clause

WHERE endView IS NULL. 

A NULL value in endView means the video is still being watched.

With the usual browser behaviour, the video is streamed to the client, often much quicker than it takes to view. To accurately report when the video is no longer being watched, you could arrange for the browser to send back a notification that the video has finished.

You might want to consider what happens when the user hits the pause button for a long time. Are they still watching that video?

Please be aware that unless you specifically disallow it in your portal, it is possible for the same user to view more than one video at once, possibly the same video, and depending upon your session managment, they may appear under the same session. This has implications for the data structures used to store the videos being viewed in the session.

To ensure reasonable performance, I would add indexes on videoid, and endView. This will support querying who are viewing a particular video, e.g.

SELECT [DISTINCT] userid FROM VideoView where videoid=? AND endView IS NULL.

The DISTINCT flag is strictly correct, but this is only for cases where the same user is viewing the video more than once. It can slow down the query, so profile and weigh up the benefit of deduplication against the cost.

On startup, you may wish to run an update query that sets endView to be startView for rows where endView is NULL. This can happen when the session is not properly closed server side (e.g. the app server crashes.)

Generally speaking, I would recommend a model similar to:

Tables

• User
• Video
• VideoView

The columns for [User] and [Video] would contain attributes specific to each entity, such as "createdate". The [VideoView] table would be a link table containing the following columns:

Columns

• VideoViewID (not null) - can be named Sno if needed
• VideoID (not null) - linked to [Video] table's primary key
• UserID (not null) - linked to [User] table's primary key
• StartDate (not null) - datetime value representing view start datetimestamp
• EndDate (null) - datetime value representing view end datetimestamp
• SessionID (not null) - uniquely identifies web server session for tracking
• Created (not null) - datetime value reporesenting when record was created / saved to db

Since you might have visitor "drop-offs" where users close their browsers before you are able to programmatically store the EndDate for a VideoView, you will probably want to post-process the data to update the EndDates have null values with the value < VideoView.StartDate + Video.Length > using an update statement and a scheduled task.

Currently viewed videos could be queried using the SQL:

select * from VideoView 
where StartDate <= getdate() 
and isnull( EndDate, getdate() ) >= getdate()

Hope that helps