設計図: 広告
DESIGN
■ TABLE
AdvertisementList
広告の登録情報です。
テーブル設計
FIELD TYPE KEY NULL DEFAULT NOTE ListID longkey PRIMARY - 主キー LastUpdate datetime - GETDATE() 最終更新日 Active bit - 0 有効／無効 UnitID shortkey FOREIGN OK 所属している広告会社 TypeID shortkey FOREIGN - 種類 SubTypeID shortkey FOREIGN - 種類 Title nvarchar/32 - 見出し Details nvarchar/200 OK 広告の紹介文 SourceCode varchar/2000 - 表示に必要な HTML コード
外部キー
FIELD REFERENCE NOTE UnitID AdvertisementCompanyList.ListID TypeID AdvertisementType SubTypeID AdvertisementType
AdvertisementType
広告の種類を示すテーブルです。
TypeID と SubTypeID でひとつの種類をあらわし、TypeID が同一のものは同一グループであるとみなします。
テーブル設計
FIELD TYPE KEY NULL DEFAULT NOTE TypeID shortkey PRIMARY - 主キー SubTypeID shortkey PRIMARY - 主キー Label nvarchar/32 - 種別名 Width smallint OK 広告の幅 Height smallint OK 広告の高さ Note nvarchar/200 OK メモ
AdvertisementCompanyList
広告サービスを提供している会社のリストです。
テーブル設計
FIELD TYPE KEY NULL DEFAULT NOTE ListID shortkey PRIMARY - 主キー CompanyID longkey FOREIGN - 会社 ID
外部キー
FIELD REFERENCE NOTE CompanyID CompanyInformation.CompanyID
■ VIEW
ViewAdvertisementList
Advertisement テーブルの一覧表示用ビューです。
ビュー設計
FIELD ORG-TABLE ORG-FIELD NOTE ListID AdvertisementList LastUpdate AdvertisementList Active AdvertisementList UnitID AdvertisementList UnitCompanyName CompanyInformation Name TypeID AdvertisementType SubTypeID AdvertisementType TypeLabel AdvertisementType Label Title AdvertisementList
実装
SELECT dbo.AdvertisementList.ListID, dbo.AdvertisementList.LastUpdate,
dbo.AdvertisementList.Active, dbo.AdvertisementList.UnitID,
dbo.CompanyInformation.Name AS UnitCompanyName,
dbo.AdvertisementType.TypeID, dbo.AdvertisementType.SubTypeID,
dbo.AdvertisementType.Label AS TypeLabel, dbo.AdvertisementList.Title
FROM dbo.CompanyInformation RIGHT OUTER JOIN
dbo.AdvertisementCompanyList ON
dbo.CompanyInformation.CompanyID = dbo.AdvertisementCompanyList.CompanyID RIGHT
OUTER JOIN
dbo.AdvertisementList LEFT OUTER JOIN
dbo.AdvertisementType ON
dbo.AdvertisementList.TypeID = dbo.AdvertisementType.TypeID AND
dbo.AdvertisementList.SubTypeID = dbo.AdvertisementType.SubTypeID ON
dbo.AdvertisementCompanyList.ListID = dbo.AdvertisementList.UnitID
ViewAdvertisementActiveList
Advertisement テーブルから Active = 1 のものを抜き出します。
ビュー設計
FIELD ORG-TABLE ORG-FIELD NOTE ListID AdvertisementList LastUpdate AdvertisementList UnitID AdvertisementList TypeID AdvertisementList SubTypeID AdvertisementList Title AdvertisementList Details AdvertisementList SourceCode AdvertisementList
実装
SELECT ListID, LastUpdate, UnitID, TypeID, SubTypeID, Title, Details, SourceCode
FROM dbo.AdvertisementList
WHERE (Active = 1)
ViewAdvertisementCompanyList
AdvertisementCompany テーブルの詳細リストを出力します。
ビュー設計
FIELD ORG-TABLE ORG-FIELD NOTE ListID AdvertisementCompany CompanyID AdvertisementCompany Name CompanyInformation LastUpdate CompanyInformation URL CompanyInformation
実装
SELECT AdvertisementCompanyList.ListID, AdvertisementCompanyList.CompanyID,
CompanyInformation.Name, CompanyInformation.LastUpdate,
CompanyInformation.URL
FROM dbo.AdvertisementCompanyList LEFT OUTER JOIN
dbo.CompanyInformation ON
dbo.AdvertisementCompanyList.CompanyID = dbo.CompanyInformation.CompanyID
■ STORED PROCEDURE
sp_AdvertisementGet
登録されている広告の中 (ViewAdvertisementActiveList) から、ランダムで１つ取り出します。
引数
PARAM TYPE DEFAULT NOTE @TypeID shortkey NULL 対象の種類 ID @UnitID shortkey NULL 対象の所属会社 ID
戻り値
PARAM TYPE NOTE ListID longkey LastUpdate datetime Active bit UnitID shortkey TypeID shortkey SubTypeID shortkey Title nvarchar/32 Details nvarchar/200 SourceCode varchar/2000
実装
CREATE PROCEDURE [sp_AdvertisementGet]
@TypeID [shortkey] = NULL,
@UnitID [shortkey] = NULL
AS
SET NOCOUNT ON
DECLARE @CURSOR CURSOR
DECLARE @ResultCount INT
DECLARE @ResultID INT
DECLARE @SEED SMALLINT
SET @SEED = DATEPART(millisecond, GETDATE())
CREATE TABLE #ActiveAdvertisement
(
[ListID] int,
[UnitID] tinyint,
[TypeID] tinyint,
[SubTypeID] tinyint,
[Title] nvarchar(32),
[Details] nvarchar(200),
[SourceCode] varchar(2000)
)
-- 既存の Active 広告を取得
INSERT #ActiveAdvertisement
SELECT
[ListID], [UnitID], [TypeID], [SubTypeID],
[Title], [Details], [SourceCode]
FROM [ViewAdvertisementActiveList]
-- TypeID による間引き
IF @TypeID IS NOT NULL
DELETE FROM #ActiveAdvertisement
WHERE [TypeID] <> @TypeID
-- UnitID による間引き
IF @UnitID IS NOT NULL
DELETE FROM #ActiveAdvertisement
WHERE [UnitID] <> @UnitID
-- 最終出力
SELECT @ResultCount = COUNT(*) FROM #ActiveAdvertisement
IF @ResultCount > 0
BEGIN
SET @ResultCount = CAST(RAND(@SEED) * 100000 AS INT) % @ResultCount + 1
SET @CURSOR = CURSOR READ_ONLY FOR
SELECT [ListID] FROM #ActiveAdvertisement
OPEN @CURSOR
WHILE @ResultCount > 0
BEGIN
FETCH FROM @CURSOR INTO @ResultID
SET @ResultCount = @ResultCount - 1
END
CLOSE @CURSOR
DEALLOCATE @CURSOR
END
SELECT * FROM #ActiveAdvertisement
WHERE [ListID] = @ResultID
sp_AdvertisementGetFromID
登録されている広告の中 (ViewAdvertisementActiveList) から、ListID を指定して、ひとつの広告を取り出します。Active フィールドの内容にかかわらずに抽出します。
引数
PARAM TYPE DEFAULT NOTE @ID longkey 対象の ListID
戻り値
PARAM TYPE NOTE ListID longkey UnitID shortkey TypeID shortkey SubTypeID shortkey Title nvarchar/32 Details nvarchar/200 SourceCode varchar/2000
実装
CREATE PROCEDURE [sp_AdvertisementGetFromID]
@ID [longkey]
AS
SELECT [ListID], [LastUpdate], [Active], [UnitID], [TypeID], [SubTypeID], [Title], [Details], [SourceCode]
FROM AdvertisementList WHERE ListID = @ID