Arnoldus
Home Page
Albums
Search
About MeExpand About Me
GuestbookExpand Guestbook
Download
MultiLang1Expand MultiLang1
Skip Navigation LinksHome Page > MultiLang1 > MultiLang2   |   Login
The Multi Language Site in C#

Multi Language Site: DataBase Structures (Part 2.1)



This is the first section of the Second Part: Multi Language Site: DataBase Structures (Part 2.1)


Multi Media

This site uses the file system to store media items (photographs, films and albums), but stores the “metadata” in the database. As most of the fields in the database are language independent, only the language dependent data have been moved to separate tables. Thus for albums the structure is:

/****** Object: Table [dbo].[Albums]    Script Date: 07/15/2008 13:19:50 ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Albums](
      [AlbumID] [int] IDENTITY(1,1) NOT NULL,
      [IsPublic] [bit] NOT NULL,
      [ThumbnailMediaID] [int] NOT NULL CONSTRAINT [DF_Albums_ThumbnailMediaID] DEFAULT ((0)),
      [Application] [nvarchar](50) NOT NULL CONSTRAINT [DF_Albums_SubSite] DEFAULT (N'Arnoldus'),
      [Directory] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Albums_1] PRIMARY KEY CLUSTERED
(
      [AlbumID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
ALTER TABLE [dbo].[Albums] ADD CONSTRAINT [DF_Albums_Application] DEFAULT (N'Arnoldus') FOR [Application]
ALTER TABLE [dbo].[Albums] ADD CONSTRAINT [DF_Albums_ThumbnailMediaID] DEFAULT ((0)) FOR [ThumbnailMediaID]
 
/****** Object: Index [PK_Albums_1]    Script Date: 07/15/2008 13:24:58 ******/
ALTER TABLE [dbo].[Albums] ADD CONSTRAINT [PK_Albums_1] PRIMARY KEY CLUSTERED
(
      [AlbumID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 
/****** Object: Index [SK_AlbumID]    Script Date: 07/15/2008 13:25:39 ******/
CREATE NONCLUSTERED INDEX [SK_AlbumID] ON [dbo].[Albums]
(
      [AlbumID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


and

/****** Object: Table [dbo].[AlbumTitles]    Script Date: 07/15/2008 13:33:19 ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AlbumTitles](
      [TitleID] [int] IDENTITY(1,1) NOT NULL,
      [AlbumID] [int] NOT NULL,
      [Title] [nvarchar](50) NOT NULL,
      [Lang] [char](2) NOT NULL,
 CONSTRAINT [PK_AlbumTitles] PRIMARY KEY CLUSTERED
(
      [TitleID] 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
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[AlbumTitles] WITH CHECK ADD CONSTRAINT [FK_AlbumTitles_Albums] FOREIGN KEY([AlbumID])
REFERENCES [dbo].[Albums] ([AlbumID])
GO
ALTER TABLE [dbo].[AlbumTitles] CHECK CONSTRAINT [FK_AlbumTitles_Albums]
 
/****** Object: Index [PK_AlbumTitles]    Script Date: 07/15/2008 13:34:12 ******/
ALTER TABLE [dbo].[AlbumTitles] ADD CONSTRAINT [PK_AlbumTitles] PRIMARY KEY CLUSTERED
(
      [TitleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 
/****** Object: Index [SK_AlbumIDs]    Script Date: 07/15/2008 13:34:49 ******/
CREATE NONCLUSTERED INDEX [SK_AlbumIDs] ON [dbo].[AlbumTitles]
(
      [AlbumID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


The tables are linked through a foreign key:

ALTER TABLE [dbo].[AlbumTitles] WITH CHECK ADD CONSTRAINT [FK_AlbumTitles_Albums] FOREIGN KEY([AlbumID])
REFERENCES [dbo].[Albums] ([AlbumID])
GO
ALTER TABLE [dbo].[AlbumTitles] CHECK CONSTRAINT [FK_AlbumTitles_Albums]
 


Simular split tables exist for “Media” and “MediaText” and “Themes” and “ThemeNames”.


Continue to: Multi Language Site: DataBase Structures (Part 2.2): Media Classes.