Hereby illustrates: 俺 is a rookie, although I have been dreaming of being a old bird, the more you learn, and you find yourself. No way, the ability is limited. This article here is actually there is no horizontal. It is just to record your own growth process, and I hope to have the same rookie with me by the way, everyone can discuss together, welcome. So if you are an old bird, it is recommended that you must estimate your own value, because I accidentally read my article, not only let you nausea, but also to delay your precious time, and then murder your life. Oops, I can't afford it. But if you have seen it, but the stomach is powerful, you should leave your handwriting again. I suggest me welcome, criticize me, I have accepted it. Some individual masters are cynical, if you disdain, you should write it here. Now in winter, you can go outside, thank you for cooperation, ^ _ ^
First, Chapter 3 System Design
1, overall design
The rationality and scalability of the overall architecture considering the overall architecture of this system, the system is prepared to develop 3-layer architecture, respectively, of the data layer, business layer, and performance layer, respectively.
This system front-end web application uses C # and ASP.NET development, and the backend database is MS SQL Server,
The video server uses REAL HELIX SERVER.
2, file directory design
The system root directory is Coofucoo. The directory of the item is VOD, and the directory below the VOD is described below:
Directory name
use
Business
Store a directory of business level
Config
Saving a directory of the configuration file
CONFIGURATION
Store the directory of the configuration class, this class belongs to the business layer
core
Store the catalog of the core class, this class is mainly used for inheritance
Data
Store data layer class
Images
Store the directory of the picture
Styles
Store the directory of the CSS file
3, database design
(1), table design
The database name is "VOD", there are two tables, respectively:
Movies table
Column name
Description
Types of
size
Is it empty?
MOVIE_ID
Number (primary key)
int
4
NO
Category_id
Movie Category Number
int
4
NO
Levell
Movie rating
nvarchar
50
YES
Title
Film name
nvarchar
50
NO
INTRO
Film introduction
TEXT
16
YES
Uptime
Movie release time
Datetime
8
NO
Showtime
Movie release time
Datetime
8
YES
Viewcount
Film click rate
Bigint
8
NO (default is 0)
Image
Related image address
nvarchar
100
NO
Address
Video viewing address
nvarchar
100
NO
Categories table
Column name
Description
Types of
size
Is it empty?
Category_id
Classification number (primary key)
int
4
NO
Name
Category Name
nvarchar
50
NO
Category_Intro
Classification description
nvarchar
100
YES
(2), relational design
Tables Movies and Categories are in touch through the field category_id.
(3), stored procedure design
All data operations in this system have stored procedures, the following is the stored procedure design:
Movies:
Store process name
parameter
description
GetMovies
@category_id Int
Returns all movie lists for the specified type, including movie details
GetMoviedetails
@Movie_id int
Returns the full content of the designated news entry
GetMoviesbycount
Return the title of movie according to the click rate
GetAllmovies
Returns all movie information according to the upload time
GetHeadines
@category_id Int
Returns all movie lists of the specified type, including only movie_id, title, uptime, viewcount, image
Insertmovie
@category_id Int
@LEVELL NVARCHAR (50)
@title nvarchar (50)
@intro text
@uptime datetime
@ShowTime DateTime
@Image nvarchar (100)
@address nvarchar (100)
OUTPUT
Insert the movie line. If there is already a row with specified category_id, title, showtime, then data parameter @ movie_id returns -1 and does not perform any insert operation. Otherwise, and return to the added movie_id
Deletemovie
@Movie_id int
Delete the movie entry for the specified ID
Updatemovie
@Movie_id int
@category_id Int
@LEVELL NVARCHAR (50)
@title nvarchar (50)
@intro text
@ShowTime DateTime
@Image nvarchar (100)
@address nvarchar (100)
Update all changes to the specified movie entry
AddViewCount
@Movie_id int
Update the specified movie entry, make it a viewcount field plus one
Categories:
Store process name
parameter
description
Getcategories
Returns all entries in the categories table
GetcategoryDetails
@category_id Int
Returns the integrity of the specified ID
GetcategoryName
Return to all categories of names and numbers
InsertCategory
@name nvarchar (50)
@category_intro nvarchar (100)
OUTPUT
Insert a new classification, if the specified classification name already exists, return -1, otherwise return new category_id
Updatecategory
@category_id Int
@name nvarchar (50)
@category_intro nvarchar (100)
Update by category_id
All Changeable content of the line specified by the ATEGORY_ID
Deletecategory
@category_id Int
Delete the line specified by the ATEGORY_ID
All stored procedures source code are provided in detail below:
GetMovies:
Create Procedure GetMovies
@category_id Int
AS
Select Movie_ID, Category_ID, Levell, Title, Intro, Uptime, Showtime, Viewcount, Image, AddressFrom Movies
WHERE CATEGORY_ID = @category_id
Go
GetMoviedetails:
Create Procedure GetMoviedetails
@Movie_id int
AS
Select Movie_ID, Category_ID, Levell, Title, Intro, Uptime, Showtime, Viewcount, Image, Address
From movies
Where movie_id = @movie_id
Go
GetMoviesbycount:
Create Procedure getMoviesbycount
AS
SELECT MOVIE_ID, TITLE
From movies
Order by ViewCount DESC
Go
GetAllMovies:
Create Procedure GetAllmovies
AS
Select Movie_ID, Levell, Title, Intro, Uptime, Showtime, Viewcount
From movies
Order by Uptime DESC
Go
GetHeadines:
Create Procedure GetHeadines
@category_id Int
AS
Select Movie_ID, Title, Uptime, Viewcount, Image
From movies
WHERE CATEGORY_ID = @category_id
Order by Uptime DESC
Go
Insertmovie:
Create Procedure Insertmovie
@category_id Int,
@LEVEL NVARCHAR (50),
@title nvarchar (50),
@Intro text,
@uptime datetime,
@ShowTime DateTime,
@Image nvarchar (100),
@address nvarchar (100),
@MOVIE_ID INT OUTPUT
AS
Declare @Currid Int
- See eti the news already exists
SELECT @Currid = Movie_ID
From movies
WHERE CATEGORY_ID = @category_id and title = @title and showtime = @showtime
- IF not, add it
IF @currid is null
Begin
Insert Into Movies
(Category_ID, Levell, Title, INTRO, UPTIME, ShowTime, Image, Address)
Values (@category_id, @LEVEL, @Title, @ITro, @uptime, @showtime, @Image, @address)
Set @Movie_id = @@ identity
IF @@ error> 0
Begin
Raiserror ('INSERT of Movies Failed ", 16, 1) Return 99
End
End
Else
Begin
SET @MOVIE_ID = -1
End
Go
Deletemovie:
Create Procedure deletemovie
@Movie_id int
AS
Delete from movies
Where movie_id = @movie_id
Go
Updatemovie:
Create Procedure Updatemovie
@Movie_id int,
@category_id Int,
@LEVELL NVARCHAR (50),
@title nvarchar (50),
@Intro text,
@ShowTime DateTime,
@Image nvarchar (100),
@address nvarchar (100)
AS
Begin Transaction Updatemovie
Update Movies
Set
Category_id = @ category_id,
Levell = @ Levell,
Title = @title,
INTRO = @intro,
Showtime = @ShowTime,
Image = @Image,
Address = @address
Where movie_id = @movie_id
IF @@ error> 0
Begin
Raiserror ('Update of Movies Failed ", 16, 1)
Rollback Transaction Updatemovie
Return 99
End
Commit Transaction Updatemovie
Go
AddViewCount:
Create Procedure AddViewCount
@Movie_id int
AS
Begin Transaction AddViewCount
Update Movies
Set
Viewcount = viewcount 1
Where movie_id = @movie_id
IF @@ error> 0
Begin
Raiserror ('AddViewCount of Movies Failed', 16, 1)
Rollback Transaction AddViewCount
Return 99
End
Commit Transaction AddPVOUNT
Go
Getcategories:
Create Procedure Getcategories
AS
Select * from categories
Go
GetcategoryDetails:
Create Procedure GetcategoryDetails
@category_id Int
AS
SELECT *
From categories
WHERE CATEGORY_ID = @category_id
Go
GetcategoryName:
Create Procedure GetcategoryName
AS
Select Name from Categories
Go
INSERTCATEGORY:
Create Procedure InsertCategory
@name nvarchar (50),
@category_intro nvarchar (100), @ category_id int output
AS
Declare @Currid Int
- See eti the category alleady exists
SELECT @Currid = Category_ID
From categories
Where name = @Name
- IF not, add it
IF @currid is null
Begin
INSERT INTO CATGORIES
(Name, Category_Intro)
VALUES (@name, @category_intro)
Set @category_id = @@ odentity
IF @@ error> 0
Begin
Raiserror ('INSERT of Categories Failed ", 16, 1)
Return 99
End
End
Else
Begin
Set @category_id = -1
End
Go
Updatecategory:
CREATE Procedure Updatecategory
@category_id Int,
@name nvarchar (50),
@category_intro nvarchar (100)
AS
Begin Transaction Updatecategory
Update categories
Set name = @Name, category_intro = @category_intro
WHERE CATEGORY_ID = @category_id
IF @@ error> 0
Begin
Raiserror ('Update of Categories Failed ", 16, 1)
Rollback Transaction Updatecategory
Return 99
End
Commit Transaction Updatecategory
Go
Deletecategory:
Create Procedure deletecategory
@category_id Int
AS
Delete from categories
WHERE CATEGORY_ID = @category_id
Go
The directory design and database design ends this, and the program code is written will be started.
Oh, I want to know the matter and listen to the decomposition. ^ _ ^
Click here to visit the previous article
Click here to visit the next article
