Database Design Question

From: Jack <jkishmael_at_hotmail.com>
Date: 4 Nov 2002 16:33:29 -0800
Message-ID: <cbeb2e2f.0211041633.64c8afc5_at_posting.google.com>

A theory question for you all! Thanks in advance for the help!

This system generates a slide show. I am trying to create a database which will store the pertinant information reguarding these slide shows. I'm hoping to get some additional information concerning my database design.

The main table which will store the information reguarding the entire 'show' is called ProgramHeader. This will include fields such as description and owner identification. For each ProgramHeader entry there will be many ProgramSlide entries, which are the 'slides' for the slide show. The ProgramSlide table will contain fields slide type, duration, sequence, and activity flag.

Now, what I would like to do is this. For each ProgramSlide, there are many ProgramElements. The ProgramElements table will track the instances of all elements which make up the ProgramSlide entry. These instances will include images, text, font parameters (html parameters), and other such information. ProgramElement will have fields location, element type, size, and an activity flag. I would like all the elements to have their own tables (image, font, color). ALL of these tables would have a primary key of ElementID. That way, I could relate them all to ProgramElements using one id field. To clarify, there would be MANY instances of image in ProgramElement and MANY ProgramElements in ProgramSlide. Is my thinking way off here?? As far as I can see, the advantages to this is that I can easily search on the key of elementid and element type but the disadvantage is that I break referencial integrity.

Is this many to many relationship effecient? I will need to search ProgramElement often - it will be updated by the client. I will need to compare it to InstallProgramElement to alter the client's application with the appropriate elements.

Please take a look at the following and let me know if this is a viable way to set up this database.
All suggestions are welcome!

/****** Object: Database AIDA Script Date: 11/4/2002 1:58:42 PM ******/
CREATE DATABASE [AIDA] ON (NAME = N'AIDA_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\AIDA_Data.MDF' , SIZE = 6, FILEGROWTH = 10%) LOG ON (NAME = N'AIDA_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\AIDA_Log.LDF' , SIZE = 12, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

use [AIDA]
GO

/****** Object: Table [dbo].[CompositeImages] Script Date: 11/4/2002 1:58:42 PM ******/
CREATE TABLE [dbo].[CompositeImages] (

[ElementID] [int] IDENTITY (1, 1) NOT NULL ,
[intCImageID] [int] NULL ,
[intCategoryID] [smallint] NOT NULL ,
[strImageName] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[intResolutionID] [tinyint] NOT NULL ,
[intExtentionID] [tinyint] NOT NULL ,
[boolActiveStatus] [tinyint] NOT NULL ,
[boolComponentStatus] [tinyint] NULL ,
[strLocation] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[strNotes] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,

[imgImage] [image] NULL ,
[strCreateID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[dteCreateDate] [datetime] NOT NULL ,
[strUpdateID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[dteUpdateDate] [datetime] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/****** Object: Table [dbo].[News] Script Date: 11/4/2002 1:58:43 PM ******/
CREATE TABLE [dbo].[News] (

[ElementId] [int] IDENTITY (1, 1) NOT NULL ,
[ProgramElementID] [int] NULL ,
[NewsTypeId] [int] NULL ,
[Headline1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Headline2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Content] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[ActiveInd] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,

[CreateID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[CreateDate] [datetime] NOT NULL

) ON [PRIMARY]
GO

/****** Object: Table [dbo].[PEEmergency] Script Date: 11/4/2002 1:58:43 PM ******/
CREATE TABLE [dbo].[PEEmergency] (

[ElementID] [int] NOT NULL ,
[EmergencyType] [int] NOT NULL ,
[EmercencyText] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Font] [int] NULL ,
[FontColor] [int] NULL ,
[BGColor] [int] NULL ,
[FontSize] [int] NULL ,
[CreateID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[CreateDate] [datetime] NOT NULL

) ON [PRIMARY]
GO

/****** Object: Table [dbo].[PEMovie] Script Date: 11/4/2002 1:58:43 PM ******/
CREATE TABLE [dbo].[PEMovie] (

[ElementID] [int] NOT NULL ,
[Duration] [int] NULL ,
[Audio] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[VideoType] [int] NULL ,
[CreateID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[CreateDate] [datetime] NOT NULL

) ON [PRIMARY]
GO

/****** Object: Table [dbo].[PESlide] Script Date: 11/4/2002 1:58:43 PM ******/
CREATE TABLE [dbo].[PESlide] (

[PESlideID] [int] IDENTITY (1, 1) NOT NULL ,
[SlideID] [int] NULL ,
[ProgramElementID] [int] NULL ,
[ElementID] [int] NOT NULL ,
[SlideType] [int] NULL ,
[CreateID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[CreateDate] [datetime] NOT NULL

) ON [PRIMARY]
GO

/****** Object: Table [dbo].[ProgramElement] Script Date: 11/4/2002 1:58:43 PM ******/
CREATE TABLE [dbo].[ProgramElement] (

[ProgramElementID] [int] IDENTITY (1, 1) NOT NULL ,
[ProgramSlideID] [int] NOT NULL ,
[ElementID] [int] NOT NULL ,
[ProgramID] [int] NOT NULL ,
[ElementType] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[FileLocation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ActiveInd] [bit] NOT NULL ,
[FileSize] [int] NULL ,
[FileSizeUnit] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,

[CreateID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[CreateDate] [datetime] NOT NULL ,
[UpdateID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UpdateDate] [datetime] NULL

) ON [PRIMARY]
GO

/****** Object: Table [dbo].[ProgramHeader] Script Date: 11/4/2002 1:58:44 PM ******/

CREATE TABLE [dbo].[ProgramHeader] (


	[FinInstID] [int] NOT NULL ,

	[ProgramID] [int] IDENTITY (1, 1) NOT NULL ,


	[ProgramName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT


NULL ,


	[NumSlide] [int] NULL ,


	[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS


NULL ,


	[Audio] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,


	[ActiveInd] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL


,


	[CreateID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT


NULL ,


	[CreateDate] [datetime] NOT NULL ,

	[UpdateID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,


	[UpdateDate] [datetime] NULL

) ON [PRIMARY]
GO

/****** Object: Table [dbo].[ProgramSlide] Script Date: 11/4/2002 1:58:44 PM ******/
CREATE TABLE [dbo].[ProgramSlide] (

[ProgramSlideID] [int] IDENTITY (1, 1) NOT NULL ,
[ProgramID] [int] NULL ,
[ProgramSlideDescription] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,

[ProgramSlideType] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,

[Duration] [int] NULL ,
[ProgramSeq] [int] NULL ,
[IsActive] [bit] NULL ,
[CreateDate] [datetime] NULL ,
[CreateID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UpdateDate] [datetime] NULL ,
[UpdateID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[SingleImages] Script Date: 11/4/2002 1:58:44 PM ******/
CREATE TABLE [dbo].[SingleImages] (

[ElementID] [int] IDENTITY (1, 1) NOT NULL ,
[intCategoryID] [smallint] NOT NULL ,
[intSubcatID] [smallint] NOT NULL ,
[strImageName] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[strPreviousName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[intSourceID] [smallint] NOT NULL ,
[intResolutionID] [tinyint] NOT NULL ,
[intExtentionID] [tinyint] NOT NULL ,
[boolActiveStatus] [tinyint] NOT NULL ,
[strLocation] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[strNotes] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[imgImage] [image] NULL ,
[strCreateID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[dteCreateDate] [datetime] NOT NULL ,
[strUpdateID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[dteUpdateDate] [datetime] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/****** Object: Table [dbo].[Weather] Script Date: 11/4/2002 1:58:44 PM ******/
CREATE TABLE [dbo].[Weather] (

[ElementID] [int] IDENTITY (1, 1) NOT NULL ,
[ProgramElementID] [int] NULL ,
[ZipCode] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[HighTemp] [smallint] NULL ,
[LowTemp] [smallint] NULL ,
[Forecast] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,

[ForecastDate] [datetime] NULL ,
[Universal] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,

[CreateID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[CreateDate] [datetime] NOT NULL

) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CompositeImages] WITH NOCHECK ADD

CONSTRAINT [DF_CompositeImages_dteCreateDate] DEFAULT (getdate()) FOR [dteCreateDate],

	CONSTRAINT [PK_CompositeImages] PRIMARY KEY  CLUSTERED 
	(
		[ElementID]
	)  ON [PRIMARY]

ALTER TABLE [dbo].[News] WITH NOCHECK ADD 
	CONSTRAINT [DF_News_CreateDate] DEFAULT (getdate()) FOR [CreateDate],
	CONSTRAINT [PK__News__7CD98669] PRIMARY KEY  CLUSTERED 
	(
		[ElementId]
	)  ON [PRIMARY]

ALTER TABLE [dbo].[PEEmergency] WITH NOCHECK ADD

CONSTRAINT [DF_PEEmergency_CreateDate] DEFAULT (getdate()) FOR [CreateDate],

	 PRIMARY KEY  CLUSTERED 
	(
		[ElementID]
	)  ON [PRIMARY]

ALTER TABLE [dbo].[PEMovie] WITH NOCHECK ADD

CONSTRAINT [DF_PEMovie_CreateDate] DEFAULT (getdate()) FOR [CreateDate],

	 PRIMARY KEY  CLUSTERED 
	(
		[ElementID]
	)  ON [PRIMARY]

ALTER TABLE [dbo].[PESlide] WITH NOCHECK ADD

CONSTRAINT [DF_PESlide_CreateDate] DEFAULT (getdate()) FOR [CreateDate],

	CONSTRAINT [PK_PESlide] PRIMARY KEY  CLUSTERED 
	(
		[PESlideID]
	)  ON [PRIMARY]

ALTER TABLE [dbo].[ProgramElement] WITH NOCHECK ADD 
	CONSTRAINT [DF_ProgramElement_ActiveInd] DEFAULT (1) FOR [ActiveInd],
	CONSTRAINT [DF_ProgramElement_CreateDate] DEFAULT (getdate()) FOR
[CreateDate],
	CONSTRAINT [PK_ProgramElement] PRIMARY KEY  CLUSTERED 
	(
		[ProgramElementID]
	)  ON [PRIMARY]

ALTER TABLE [dbo].[ProgramHeader] WITH NOCHECK ADD

CONSTRAINT [DF_ProgramHeader_CreateDate] DEFAULT (getdate()) FOR [CreateDate],

	CONSTRAINT [PK__ProgramHeader__0DAF0CB0] PRIMARY KEY  CLUSTERED 
	(
		[ProgramID]
	)  ON [PRIMARY]

ALTER TABLE [dbo].[ProgramSlide] WITH NOCHECK ADD

CONSTRAINT [DF_ProgramSlide_CreateDate] DEFAULT (getdate()) FOR [CreateDate],

	CONSTRAINT [PK_ProgramSlide] PRIMARY KEY  CLUSTERED 
	(
		[ProgramSlideID]
	)  ON [PRIMARY]

ALTER TABLE [dbo].[SingleImages] WITH NOCHECK ADD

CONSTRAINT [DF_SingleImages_dteCreateDate] DEFAULT (getdate()) FOR [dteCreateDate],

	CONSTRAINT [PK_SingleImages] PRIMARY KEY  CLUSTERED 
	(
		[ElementID]
	)  ON [PRIMARY]

ALTER TABLE [dbo].[Weather] WITH NOCHECK ADD

CONSTRAINT [DF_Weather_CreateDate] DEFAULT (getdate()) FOR [CreateDate],

	CONSTRAINT [PK__Weather__1DE57479] PRIMARY KEY  CLUSTERED 
	(
		[ElementID]
	)  ON [PRIMARY]

ALTER TABLE [dbo].[News] ADD

	CONSTRAINT [FK_News_LNewsType] FOREIGN KEY 
	(
		[NewsTypeId]
	) REFERENCES [dbo].[LNewsType] (
		[NewsTypeID]
	)

ALTER TABLE [dbo].[ProgramElement] ADD

	CONSTRAINT [FK_ProgramElement_CompositeImages] FOREIGN KEY 
	(
		[ElementID]
	) REFERENCES [dbo].[CompositeImages] (
		[ElementID]
	),
	CONSTRAINT [FK_ProgramElement_News] FOREIGN KEY 
	(
		[ElementID]
	) REFERENCES [dbo].[News] (
		[ElementId]
	),
	CONSTRAINT [FK_ProgramElement_PEEmergency] FOREIGN KEY 
	(
		[ElementID]
	) REFERENCES [dbo].[PEEmergency] (
		[ElementID]
	),
	CONSTRAINT [FK_ProgramElement_PEMovie] FOREIGN KEY 
	(
		[ElementID]
	) REFERENCES [dbo].[PEMovie] (
		[ElementID]
	),
	CONSTRAINT [FK_ProgramElement_ProgramSlide] FOREIGN KEY 
	(
		[ProgramSlideID]
	) REFERENCES [dbo].[ProgramSlide] (
		[ProgramSlideID]
	),
	CONSTRAINT [FK_ProgramElement_SingleImages] FOREIGN KEY 
	(
		[ElementID]
	) REFERENCES [dbo].[SingleImages] (
		[ElementID]
	),
	CONSTRAINT [FK_ProgramElement_Weather] FOREIGN KEY 
	(
		[ElementID]
	) REFERENCES [dbo].[Weather] (
		[ElementID]
	)

ALTER TABLE [dbo].[ProgramSlide] ADD

	CONSTRAINT [FK_ProgramSlide_ProgramHeader] FOREIGN KEY 
	(
		[ProgramID]
	) REFERENCES [dbo].[ProgramHeader] (
		[ProgramID]
	)

GO Received on Tue Nov 05 2002 - 01:33:29 CET

This message: [ Message body ]
Next message: D Guntermann: "Re: The Practical Benefits of the Relational Model"
Previous message: Pablo Sanchez: "Re: Trigger while reading row?"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message