Сейчас я планирую все видео (фильм, сериал) пихать в 1 таблицу с меткой типа.
Все фильмы в одной таблице. По типам распределяется в представлении:
CREATE TABLE [Film](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Heading] [int] NULL,
[HeadingName] [varchar](20) NULL,
[HeadingClose] [bit] NULL,
[SeriesName] [varchar](15) NULL,
[Series] [smallint] NULL,
[SeriesDigit] [smallint] NULL,
[Season] [smallint] NULL,
[Episode] [smallint] NULL,
[PartName] [varchar](15) NULL,
[Part] [smallint] NULL,
[HeadingNumber] AS (isnull(isnull([SeriesName]+' ','серия ')+CONVERT([varchar],[Series]),'')+isnull((('s'+right('00'+CONVERT([varchar],[Season]),(2)))+'e')+right('00'+CONVERT([varchar],[Episode]),(2)),'')),
[Serias] [smallint] NULL,
[Article] [varchar](20) NULL,
[Name] [varchar](200) NULL,
[NameTranslat] [varchar](200) NULL,
[Year] [smallint] NULL,
[Premiere] [smalldatetime] NULL,
[Country] [varchar](50) NULL,
[Addition] [varchar](200) NULL,
[TitleArticleLess] AS ((([Name]+isnull((' ('+[NameTranslat])+')',''))+isnull((' ['+[Addition])+']',''))+isnull(((', '+isnull([PartName],'часть'))+' ')+CONVERT([varchar],[Part]),'')),
[TitleBase] AS ((((isnull([Article]+' ','')+[Name])+isnull((' ('+[NameTranslat])+')',''))+isnull((' ['+[Addition])+']',''))+isnull(((', '+isnull([PartName],'часть'))+' ')+CONVERT([varchar],[Part]),'')),
[TitleYear] AS (((((isnull([Article]+' ','')+[Name])+isnull((' ('+[NameTranslat])+')',''))+isnull((' ['+[Addition])+']',''))+isnull(((', '+isnull([PartName],'часть'))+' ')+CONVERT([varchar],[Part]),''))+isnull(' · '+CONVERT([varchar],[Year],(104)),'')),
[TitleCountry] AS (((((isnull([Article]+' ','')+[Name])+isnull((' ('+[NameTranslat])+')',''))+isnull((' ['+[Addition])+']',''))+isnull(((', '+isnull([PartName],'часть'))+' ')+CONVERT([varchar],[Part]),''))+isnull(' · '+[Country],'')),
[TitleYearCountry] AS ((((((isnull([Article]+' ','')+[Name])+isnull((' ('+[NameTranslat])+')',''))+isnull((' ['+[Addition])+']',''))+isnull(((', '+isnull([PartName],'часть'))+' ')+CONVERT([varchar],[Part]),''))+isnull(' · '+CONVERT([varchar],[Year],(104)),''))+isnull(' · '+[Country],'')),
[Cover] [image] NULL,
[Note] [text] NULL,
[NotWatch] [bit] NULL,
[LastWatch] [datetime] NULL,
[DateWatch] [datetime] NULL,
[WatchDisplay] AS (isnull('+ '+CONVERT([varchar],[DateWatch],(104)),'')+isnull(case when [DateWatch] IS NULL then CONVERT([varchar],[LastWatch],(104)) else (' ('+CONVERT([varchar],[LastWatch],(104)))+')' end,'')),
[Range] AS ((((((isnull(CONVERT([varchar](4),[Year]),'1895')+right('00'+CONVERT([varchar],isnull([Series],(0))),(3)))+right('00'+CONVERT([varchar],isnull([Season],(0))),(3)))+right('00'+CONVERT([varchar],isnull([Episode],(0))),(3)))+isnull(CONVERT([varchar](8),[Premiere],(112)),'00000000'))+right('00'+CONVERT([varchar],isnull([Part],(0))),(3)))+isnull(upper([Name]),'')),
[SearchValue] AS (replace(rtrim([Name]),' ','+')),
[Icon] AS (CONVERT([bit],isnull(datalength([Note]),(0)),(0))+CONVERT([bit],isnull(datalength([Cover]),(0)),(0))*(2)),
[AttributeIcon] [bit] NULL,
CONSTRAINT [PK_Film] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [Film] ADD CONSTRAINT [DF_Film_AttributeIcon] DEFAULT ((0)) FOR [AttributeIcon]
GO
ALTER TABLE [Film] WITH CHECK ADD CONSTRAINT [FK_Film_Film] FOREIGN KEY([Heading])
REFERENCES [dbo].[Film] ([ID])
GO
ALTER TABLE [Film] CHECK CONSTRAINT [FK_Film_Film]
GO
Всех актеров, команду (режис., сценарист, и т.д.)
Все атрибуты фильма в нескольких таблицах.
Группы атрибутов
CREATE TABLE [FilmAttributeGroup](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[Person] [bit] NULL,
[Under] [bit] NULL,
[Range] [smallint] NOT NULL,
[Uses] [int] NOT NULL,
CONSTRAINT [PK_FilmAttributeGroup] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [FilmAttributeGroup] ADD CONSTRAINT [DF_FilmAttributeGroup_Range] DEFAULT ((0)) FOR [Range]
GO
ALTER TABLE [FilmAttributeGroup] ADD CONSTRAINT [DF_FilmAttributeGroup_Uses] DEFAULT ((0)) FOR [Uses]
GO
Значения атрибутов
CREATE TABLE [FilmAttributeValue](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Group] [smallint] NOT NULL,
[Value] [varchar](300) NOT NULL,
[ValueLeft] AS (rtrim(case when charindex('(',[Value])>(0) then left([Value],charindex('(',[Value])-(1)) else [Value] end)),
[ValueRight] AS (rtrim(case when charindex('(',[Value])>(0) then substring([Value],charindex('(',[Value])+(1),(charindex(')',[Value])-charindex('(',[Value]))-(1)) end)),
[SearchValue] AS (replace(rtrim(case when charindex('(',[Value])>(0) then left([Value],charindex('(',[Value])-(1)) else [Value] end),' ','+')),
[Person] [int] NULL,
[Equalize] [bit] NOT NULL,
[Uses] [int] NULL,
CONSTRAINT [PK_FilmAttributeValue] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [FilmAttributeValue] ADD CONSTRAINT [DF_FilmAttributeValue_Equalize] DEFAULT ((1)) FOR [Equalize]
GO
ALTER TABLE [FilmAttributeValue] WITH CHECK ADD CONSTRAINT [FK_FilmAttributeValue_FilmAttributeGroup] FOREIGN KEY([Group])
REFERENCES [dbo].[FilmAttributeGroup] ([ID])
ON DELETE CASCADE
GO
ALTER TABLE [FilmAttributeValue] CHECK CONSTRAINT [FK_FilmAttributeValue_FilmAttributeGroup]
GO
ALTER TABLE [FilmAttributeValue] WITH CHECK ADD CONSTRAINT [FK_FilmAttributeValue_Person] FOREIGN KEY([Person])
REFERENCES [dbo].[Person] ([ID])
ON DELETE SET NULL
GO
ALTER TABLE [FilmAttributeValue] CHECK CONSTRAINT [FK_FilmAttributeValue_Person]
GO
Атрибуты фильма
REATE TABLE [FilmAttribute](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Film] [int] NOT NULL,
[Attribute] [int] NOT NULL,
[Addition] [varchar](250) NULL,
[GroupEdit] [int] NULL,
CONSTRAINT [PK_FilmAttribute] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [FilmAttribute] WITH CHECK ADD CONSTRAINT [FK_FilmAttribute_Film] FOREIGN KEY([Film])
REFERENCES [dbo].[Film] ([ID])
GO
ALTER TABLE [FilmAttribute] CHECK CONSTRAINT [FK_FilmAttribute_Film]
GO
ALTER TABLE [FilmAttribute] WITH CHECK ADD CONSTRAINT [FK_FilmAttribute_FilmAttributeValue] FOREIGN KEY([Attribute])
REFERENCES [dbo].[FilmAttributeValue] ([ID])
GO
ALTER TABLE [FilmAttribute] CHECK CONSTRAINT [FK_FilmAttribute_FilmAttributeValue]
GO
Персоны
CREATE TABLE [Person](
[ID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](100) NULL,
[LastName] [varchar](100) NOT NULL,
[NameRussian] [varchar](200) NULL,
[BirthDay] [smalldatetime] NULL,
[BirthPlace] [varchar](100) NULL,
[DeathDay] [smalldatetime] NULL,
[DeathPlace] [varchar](100) NULL,
[Text] [text] NULL,
[Picture] [image] NULL,
[Uses] [int] NULL,
[NameFull] AS ((isnull([FirstName]+' ','')+ltrim([LastName]))+isnull((' ('+[NameRussian])+')','')),
[NameOrder] AS ((ltrim([LastName])+isnull(', '+[FirstName],''))+isnull((' ('+[NameRussian])+')','')),
[SearchValue] AS (isnull(replace([FirstName],' ','+')+'+','')+replace([LastName],' ','+')),
[NoteIcon] AS (CONVERT([bit],isnull(datalength([Text]),(0)),(0))+CONVERT([bit],isnull(datalength([Picture]),(0)),(0))*(2)),
[Key] AS ('P'+CONVERT([varchar],[ID])),
CONSTRAINT [PK_Performer] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [Person] ADD CONSTRAINT [DF_Person_Uses] DEFAULT ((0)) FOR [Uses]
GO
Простой
Простой
