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

/*
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.

Show quoteHide quote

sql weird
NOLOCK
INSERT table (column1, column2) (@variable, '100')
Simple JOIN turned ugly - Help!
Is the SQL Server Service Running?