Sunday, February 19, 2012

Database Design

Hello,
I am not so experienced in database design and in my job now I have to
design a database for publications. The problem is the design should be
worked out as soon as possible.:( The publication database should contain th
e
following details:
Title
Subtitle
Authors
Place of publication
Year of publication
Number of sites
Keywords
There could be several authors and several keywords.
Could anybody tell me a good design for this attributes and what database
will be fit best?
thanks in advance
lg
max/*
Here is a quick, down and dirty design (SQL Server or MSDE only, as it is
T-SQL). Copy and paste to Query Analyzer and take a look:
NOTE: Commented out so you can copy the entire post and run it in Query
Analyzer.
*/
CREATE TABLE [dbo].[Author] (
[AuthorID] [int] IDENTITY (1, 1) NOT NULL ,
[AuthorName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[AuthorPublicationAssoc] (
[AuthorPublicationAssocID] [int] IDENTITY (1, 1) NOT NULL ,
[AuthorID] [int] NOT NULL ,
[PublicationID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Keyword] (
[KeywordID] [int] IDENTITY (1, 1) NOT NULL ,
[KeywordName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[KeywordPublicationAssoc] (
[KeywordPublicationAssocID] [int] IDENTITY (1, 1) NOT NULL ,
[KeywordID] [int] NOT NULL ,
[PublicationID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Place] (
[PlaceID] [int] IDENTITY (1, 1) NOT NULL ,
[PlaceName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Publication] (
[PublicationID] [int] IDENTITY (1, 1) NOT NULL ,
[PlaceID] [int] NULL ,
[PublicationName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[PublicationYear] [smallint] NOT NULL ,
[NumberOfSites] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Author] WITH NOCHECK ADD
CONSTRAINT [PK_Author] PRIMARY KEY CLUSTERED
(
[AuthorID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AuthorPublicationAssoc] WITH NOCHECK ADD
CONSTRAINT [PK_AuthorPublicationAssoc] PRIMARY KEY CLUSTERED
(
[AuthorPublicationAssocID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Keyword] WITH NOCHECK ADD
CONSTRAINT [PK_Keyword] PRIMARY KEY CLUSTERED
(
[KeywordID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[KeywordPublicationAssoc] WITH NOCHECK ADD
CONSTRAINT [PK_KeywordPublicationAssoc] PRIMARY KEY CLUSTERED
(
[KeywordPublicationAssocID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Place] WITH NOCHECK ADD
CONSTRAINT [PK_Place] PRIMARY KEY CLUSTERED
(
[PlaceID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Publication] WITH NOCHECK ADD
CONSTRAINT [PK_Publication] PRIMARY KEY CLUSTERED
(
[PublicationID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Publication] ADD
CONSTRAINT [DF_Publication_NumberOfSites] DEFAULT (0) FOR [NumberOfSites]
GO
ALTER TABLE [dbo].[AuthorPublicationAssoc] ADD
CONSTRAINT [FK_AuthorPublicationAssoc_Author] FOREIGN KEY
(
[AuthorID]
) REFERENCES [dbo].[Author] (
[AuthorID]
),
CONSTRAINT [FK_AuthorPublicationAssoc_Publication] FOREIGN KEY
(
[PublicationID]
) REFERENCES [dbo].[Publication] (
[PublicationID]
)
GO
ALTER TABLE [dbo].[KeywordPublicationAssoc] ADD
CONSTRAINT [FK_KeywordPublicationAssoc_Keyword] FOREIGN KEY
(
[KeywordID]
) REFERENCES [dbo].[Keyword] (
[KeywordID]
),
CONSTRAINT [FK_KeywordPublicationAssoc_Publication]
FOREIGN KEY
(
[PublicationID]
) REFERENCES [dbo].[Publication] (
[PublicationID]
)
GO
ALTER TABLE [dbo].[Publication] ADD
CONSTRAINT [FK_Publication_Place] FOREIGN KEY
(
[PlaceID]
) REFERENCES [dbo].[Place] (
[PlaceID]
)
GO
/*
This is just basic layout, so alter to your hearts content.
As for database, that really depends on the app and the amount of traffic. I
like SQL Server (and assume you do to since you are posting in a SQL Server
group), but it is not the only database out there.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"max" wrote:

> Hello,
> I am not so experienced in database design and in my job now I have to
> design a database for publications. The problem is the design should be
> worked out as soon as possible.:( The publication database should contain
the
> following details:
> Title
> Subtitle
> Authors
> Place of publication
> Year of publication
> Number of sites
> Keywords
> There could be several authors and several keywords.
> Could anybody tell me a good design for this attributes and what database
> will be fit best?
> thanks in advance
> lg
> max
*/

No comments:

Post a Comment