Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

SQL 2008 Table Design Suggestions

I am re-doing a project for my company and I am looking for suggestions and review of the basic table design before I start designing the actual database schema. I want to get it right, and I'd appreciate some feedback from the Experienced SQL people out there. I have attached a discussion of the project for your review.
I have some MS SQL experience but most of it has been fairly simple. This project "SMELLS" like a good relational design is needed.
Thanks ahead of time if you take the time to look at this.

~Dave NewProject.pdf
0
NaplesFLDave
Asked:
NaplesFLDave
  • 9
  • 7
1 Solution
 
EyalCommented:
hope I understood your logic

design
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Attributes
	(
	ID int NOT NULL IDENTITY (1, 1),
	Name nvarchar(50) NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Attributes ADD CONSTRAINT
	PK_Attributes PRIMARY KEY CLUSTERED 
	(
	ID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.Attributes SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Lots
	(
	ID int NOT NULL IDENTITY (1, 1),
	Code nvarchar(16) NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Lots ADD CONSTRAINT
	PK_Lots PRIMARY KEY CLUSTERED 
	(
	ID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.Lots SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.LotsAttributes
	(
	LotID int NOT NULL,
	AttributeID int NOT NULL,
	Value nvarchar(MAX) NULL
	)  ON [PRIMARY]
	 TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.LotsAttributes ADD CONSTRAINT
	PK_LotsAttributes PRIMARY KEY CLUSTERED 
	(
	LotID,
	AttributeID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.LotsAttributes ADD CONSTRAINT
	FK_LotsAttributes_Lots FOREIGN KEY
	(
	LotID
	) REFERENCES dbo.Lots
	(
	ID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.LotsAttributes ADD CONSTRAINT
	FK_LotsAttributes_Attributes FOREIGN KEY
	(
	AttributeID
	) REFERENCES dbo.Attributes
	(
	ID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.LotsAttributes SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

Open in new window

0
 
NaplesFLDaveAuthor Commented:
Under LOTS, the CODE is a UNIQUE -once entered value- so we could do away with the ID entity.
Unless there is another reason for it.
I need more detail,sorry, to see the complete layout.

Thanks,

~Dave
0
 
EyalCommented:
it's ok, I started small so I will know if i'm in the correct path

It's not recommended to have primary key as character based field
the foreign keys will work slower
you can create unique constraint on the code field to validate data(I skipped this one in the code)

do you have predefined values for each attribute? if yes then I will do it even more strict (tell me if it like so and I will do the change)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
NaplesFLDaveAuthor Commented:
Thank you. Yes all of the Codes in the LOT ID have pre-defined Values.
The PICK LISTS will be Entered and pre-filed before the LOTID is generated.
Good tip on the LOTID "CODE" fileld. I will need to keep the Seperate entities stored seperately in the LOTID table anyway so I can FIlter on thlem later in the reporting process, "I Believe".
That will cut down on the Fancy Stuff behind the scenes later if necessary. String parcing..etc kept to a minimum.
0
 
EyalCommented:
great we are progressing
my question was regarding the attributes. are all the possible options for each attribute is predefined?
0
 
NaplesFLDaveAuthor Commented:
Well... The Attributes that are for the Major Categories like CAR / BALL / TRUCk for example, ARE pre-defined right now. And the Attributes for those categories are as well. And that goes for the DEFECTS item and their Attributes for each one of those items.
They ARE subject to change / IE: The owners might want to start tracking Umbrellas which they don't do now. Or they might want to add SMELL to the CAR attribute Defect item.

Or add Rolls OK to the BALL defect item in the future.

~Dave
0
 
NaplesFLDaveAuthor Commented:
I understand  that New tables might have to be added to the schema for UMBRELLAs and their attributes. I just want to make sure that the schema structure will allow it without a Major redesign later.
0
 
EyalCommented:
consider this as more striked version

designB
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Attributes
	(
	ID int NOT NULL IDENTITY (1, 1),
	Name nvarchar(50) NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Attributes ADD CONSTRAINT
	PK_Attributes PRIMARY KEY CLUSTERED 
	(
	ID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.Attributes SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.AttributeOptions
	(
	ID int NOT NULL IDENTITY (1, 1),
	AttributeID int NOT NULL,
	Value nvarchar(MAX) NOT NULL
	)  ON [PRIMARY]
	 TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.AttributeOptions ADD CONSTRAINT
	PK_AttributeOptions PRIMARY KEY CLUSTERED 
	(
	ID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.AttributeOptions ADD CONSTRAINT
	FK_AttributeOptions_Attributes FOREIGN KEY
	(
	AttributeID
	) REFERENCES dbo.Attributes
	(
	ID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.AttributeOptions SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Lots
	(
	ID int NOT NULL IDENTITY (1, 1),
	Code nvarchar(16) NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Lots ADD CONSTRAINT
	PK_Lots PRIMARY KEY CLUSTERED 
	(
	ID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.Lots ADD CONSTRAINT
	UNQ_Lots UNIQUE NONCLUSTERED 
	(
	Code
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.Lots SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.LotsAttributes
	(
	LotID int NOT NULL,
	AttributeOptionID int NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.LotsAttributes ADD CONSTRAINT
	PK_LotsAttributes PRIMARY KEY CLUSTERED 
	(
	LotID,
	AttributeOptionID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.LotsAttributes ADD CONSTRAINT
	FK_LotsAttributes_Lots FOREIGN KEY
	(
	LotID
	) REFERENCES dbo.Lots
	(
	ID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.LotsAttributes ADD CONSTRAINT
	FK_LotsAttributes_AttributeOptions FOREIGN KEY
	(
	AttributeOptionID
	) REFERENCES dbo.AttributeOptions
	(
	ID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.LotsAttributes SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

Open in new window

0
 
NaplesFLDaveAuthor Commented:
OK. I have to digest this a bit. Is this the total table schema or is there one for CARS  and one for BALLS and one for TRUCKS..etc. I suspect this is it.

~Dave
0
 
EyalCommented:
you don't need to create another tables to support other entities.

what is UMBRELLA?

I think I didn't got your request fine.
is this the hierarchy of the entities?
lots
-->cars
----->attributes
0
 
NaplesFLDaveAuthor Commented:
I think you have it correct.
The LOTID is the main table that has the ACCOUNTING system tracking number.
Then there is the Main Category, like CAR or BOAT or BALL.
Each of those items will have their specific Attributes that we want to track.
In one case "DEFECTS" that is an attribute for the CAR and others that has Multiple items to track as well.
Like DEFECT
             PAINT       yes
             TIRES         no
             ENGINE      no
Each of the Categories will have their own Attributes to track. Both at the category level and in the DEFECT sub level.

"UMBRELLA" is just Another category that was used as an example of a new Category of product to track.
0
 
EyalCommented:
OK... so this is how it should be
USE [tmp]
GO
/****** Object:  Table [dbo].[Lots]    Script Date: 10/17/2011 15:27:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Lots](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Code] [nvarchar](16) NOT NULL,
 CONSTRAINT [PK_Lots] 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],
 CONSTRAINT [UNQ_Lots] UNIQUE NONCLUSTERED 
(
	[Code] 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
/****** Object:  Table [dbo].[Categories]    Script Date: 10/17/2011 15:27:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Categories](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Categories] 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
/****** Object:  Table [dbo].[Attributes]    Script Date: 10/17/2011 15:27:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Attributes](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Attributes] 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
/****** Object:  Table [dbo].[Items]    Script Date: 10/17/2011 15:27:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Items](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[LotID] [int] NOT NULL,
	[CategoryID] [int] NOT NULL,
	[Name] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_Items] 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
/****** Object:  Table [dbo].[AttributeOptions]    Script Date: 10/17/2011 15:27:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AttributeOptions](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[AttributeID] [int] NOT NULL,
	[Value] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_AttributeOptions] 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
/****** Object:  Table [dbo].[ItemsAttributes]    Script Date: 10/17/2011 15:27:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ItemsAttributes](
	[ItemID] [int] NOT NULL,
	[AttributeOptionID] [int] NOT NULL,
 CONSTRAINT [PK_ItemsAttributes] PRIMARY KEY CLUSTERED 
(
	[ItemID] ASC,
	[AttributeOptionID] 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
/****** Object:  ForeignKey [FK_AttributeOptions_Attributes]    Script Date: 10/17/2011 15:27:08 ******/
ALTER TABLE [dbo].[AttributeOptions]  WITH CHECK ADD  CONSTRAINT [FK_AttributeOptions_Attributes] FOREIGN KEY([AttributeID])
REFERENCES [dbo].[Attributes] ([ID])
GO
ALTER TABLE [dbo].[AttributeOptions] CHECK CONSTRAINT [FK_AttributeOptions_Attributes]
GO
/****** Object:  ForeignKey [FK_Items_Categories]    Script Date: 10/17/2011 15:27:08 ******/
ALTER TABLE [dbo].[Items]  WITH CHECK ADD  CONSTRAINT [FK_Items_Categories] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[Categories] ([ID])
GO
ALTER TABLE [dbo].[Items] CHECK CONSTRAINT [FK_Items_Categories]
GO
/****** Object:  ForeignKey [FK_Items_Lots]    Script Date: 10/17/2011 15:27:08 ******/
ALTER TABLE [dbo].[Items]  WITH CHECK ADD  CONSTRAINT [FK_Items_Lots] FOREIGN KEY([LotID])
REFERENCES [dbo].[Lots] ([ID])
GO
ALTER TABLE [dbo].[Items] CHECK CONSTRAINT [FK_Items_Lots]
GO
/****** Object:  ForeignKey [FK_ItemsAttributes_AttributeOptions]    Script Date: 10/17/2011 15:27:08 ******/
ALTER TABLE [dbo].[ItemsAttributes]  WITH CHECK ADD  CONSTRAINT [FK_ItemsAttributes_AttributeOptions] FOREIGN KEY([AttributeOptionID])
REFERENCES [dbo].[AttributeOptions] ([ID])
GO
ALTER TABLE [dbo].[ItemsAttributes] CHECK CONSTRAINT [FK_ItemsAttributes_AttributeOptions]
GO
/****** Object:  ForeignKey [FK_ItemsAttributes_Items]    Script Date: 10/17/2011 15:27:08 ******/
ALTER TABLE [dbo].[ItemsAttributes]  WITH CHECK ADD  CONSTRAINT [FK_ItemsAttributes_Items] FOREIGN KEY([ItemID])
REFERENCES [dbo].[Items] ([ID])
GO
ALTER TABLE [dbo].[ItemsAttributes] CHECK CONSTRAINT [FK_ItemsAttributes_Items]
GO

Open in new window

0
 
NaplesFLDaveAuthor Commented:
Can you re-send that and a entity diagram?
The text is garbled up a bit.

Thanks,

*Dave
0
 
NaplesFLDaveAuthor Commented:
So if I get this so far...
The LOTS table is the master table that holds my LOTID value (unique) for each item.
The LOTSATTRIBUTES table holds my Main categories like CAR / TRUCK / BALL.
The ATTRIBUTEOPTIONS hold the items I wish to track for the CAR / TRUCK / BALL.
And the ATTRIBUTES Table holds the Defects items I want to track from the DEFECT item in the ATTRIBUTEOPTIONS Table.

That means the LOTSATTRIBUTES Table Holds the values IE:
CODE    AA
VALUE  CAR

CODE   AB
VALUE  TRUCK

ETC..

And the AttributeOptions Table has these types of items:

PAINT
DENTS
COLOR
DEFECTS

That's where I get lost.

BALLS don't have PAINT or DENTS...?

*Dave
0
 
EyalCommented:
you didn't got it right

design
lots (state1,state2...)
Items (car1,car2,track5,umbrela33,bike2,...)
categories (Car,Track,Bike,Umbrella)
Attributes (PAINT,DENTS,COLOR,DEFECTS)
AttributeOptions (for paint attribute you will have: Metal,Mat ,for color attribute you will have: reg,grean,yellow...., )
CategoryAttributes - defines what attributes each category can have (you can use same attribute for different categories)
ItemsAttributes - holds the selected value for each attribute for specific Item

updated script:
/****** Object:  Database [temp]    Script Date: 10/18/2011 06:52:33 ******/
CREATE DATABASE [temp] ON  PRIMARY
( NAME = N'temp', FILENAME = N'c:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\temp.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'temp_log', FILENAME = N'c:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\temp_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [temp] SET COMPATIBILITY_LEVEL = 90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [temp].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [temp] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [temp] SET ANSI_NULLS OFF
GO
ALTER DATABASE [temp] SET ANSI_PADDING OFF
GO
ALTER DATABASE [temp] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [temp] SET ARITHABORT OFF
GO
ALTER DATABASE [temp] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [temp] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [temp] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [temp] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [temp] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [temp] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [temp] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [temp] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [temp] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [temp] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [temp] SET  DISABLE_BROKER
GO
ALTER DATABASE [temp] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [temp] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [temp] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [temp] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [temp] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [temp] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [temp] SET  READ_WRITE
GO
ALTER DATABASE [temp] SET RECOVERY SIMPLE
GO
ALTER DATABASE [temp] SET  MULTI_USER
GO
ALTER DATABASE [temp] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [temp] SET DB_CHAINING OFF
GO
USE [temp]
GO
/****** Object:  Table [dbo].[Lots]    Script Date: 10/18/2011 06:52:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Lots](
      [ID] [int] IDENTITY(1,1) NOT NULL,
      
 [nvarchar](16) NOT NULL,
 CONSTRAINT [PK_Lots] 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],
 CONSTRAINT [UNQ_Lots] UNIQUE NONCLUSTERED 
(
	[Code] 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
/****** Object:  Table [dbo].[Categories]    Script Date: 10/18/2011 06:52:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Categories](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Categories] 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
/****** Object:  Table [dbo].[Attributes]    Script Date: 10/18/2011 06:52:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Attributes](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Attributes] 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
/****** Object:  Table [dbo].[Items]    Script Date: 10/18/2011 06:52:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Items](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[LotID] [int] NOT NULL,
	[CategoryID] [int] NOT NULL,
	[Name] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_Items] 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
/****** Object:  Table [dbo].[CategoryAttributes]    Script Date: 10/18/2011 06:52:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CategoryAttributes](
	[CategoryID] [int] NOT NULL,
	[AttributeID] [int] NOT NULL,
 CONSTRAINT [PK_CategoryAttributes] PRIMARY KEY CLUSTERED 
(
	[CategoryID] ASC,
	[AttributeID] 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
/****** Object:  Table [dbo].[AttributeOptions]    Script Date: 10/18/2011 06:52:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AttributeOptions](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[AttributeID] [int] NOT NULL,
	[Value] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_AttributeOptions] 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
/****** Object:  Table [dbo].[ItemsAttributes]    Script Date: 10/18/2011 06:52:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ItemsAttributes](
	[ItemID] [int] NOT NULL,
	[AttributeOptionID] [int] NOT NULL,
 CONSTRAINT [PK_ItemsAttributes] PRIMARY KEY CLUSTERED 
(
	[ItemID] ASC,
	[AttributeOptionID] 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
/****** Object:  ForeignKey [FK_Items_Categories]    Script Date: 10/18/2011 06:52:33 ******/
ALTER TABLE [dbo].[Items]  WITH CHECK ADD  CONSTRAINT [FK_Items_Categories] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[Categories] ([ID])
GO
ALTER TABLE [dbo].[Items] CHECK CONSTRAINT [FK_Items_Categories]
GO
/****** Object:  ForeignKey [FK_Items_Lots]    Script Date: 10/18/2011 06:52:33 ******/
ALTER TABLE [dbo].[Items]  WITH CHECK ADD  CONSTRAINT [FK_Items_Lots] FOREIGN KEY([LotID])
REFERENCES [dbo].[Lots] ([ID])
GO
ALTER TABLE [dbo].[Items] CHECK CONSTRAINT [FK_Items_Lots]
GO
/****** Object:  ForeignKey [FK_CategoryAttributes_Attributes]    Script Date: 10/18/2011 06:52:33 ******/
ALTER TABLE [dbo].[CategoryAttributes]  WITH CHECK ADD  CONSTRAINT [FK_CategoryAttributes_Attributes] FOREIGN KEY([AttributeID])
REFERENCES [dbo].[Attributes] ([ID])
GO
ALTER TABLE [dbo].[CategoryAttributes] CHECK CONSTRAINT [FK_CategoryAttributes_Attributes]
GO
/****** Object:  ForeignKey [FK_CategoryAttributes_Categories]    Script Date: 10/18/2011 06:52:33 ******/
ALTER TABLE [dbo].[CategoryAttributes]  WITH CHECK ADD  CONSTRAINT [FK_CategoryAttributes_Categories] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[Categories] ([ID])
GO
ALTER TABLE [dbo].[CategoryAttributes] CHECK CONSTRAINT [FK_CategoryAttributes_Categories]
GO
/****** Object:  ForeignKey [FK_AttributeOptions_Attributes]    Script Date: 10/18/2011 06:52:33 ******/
ALTER TABLE [dbo].[AttributeOptions]  WITH CHECK ADD  CONSTRAINT [FK_AttributeOptions_Attributes] FOREIGN KEY([AttributeID])
REFERENCES [dbo].[Attributes] ([ID])
GO
ALTER TABLE [dbo].[AttributeOptions] CHECK CONSTRAINT [FK_AttributeOptions_Attributes]
GO
/****** Object:  ForeignKey [FK_ItemsAttributes_AttributeOptions]    Script Date: 10/18/2011 06:52:33 ******/
ALTER TABLE [dbo].[ItemsAttributes]  WITH CHECK ADD  CONSTRAINT [FK_ItemsAttributes_AttributeOptions] FOREIGN KEY([AttributeOptionID])
REFERENCES [dbo].[AttributeOptions] ([ID])
GO
ALTER TABLE [dbo].[ItemsAttributes] CHECK CONSTRAINT [FK_ItemsAttributes_AttributeOptions]
GO
/****** Object:  ForeignKey [FK_ItemsAttributes_Items]    Script Date: 10/18/2011 06:52:33 ******/
ALTER TABLE [dbo].[ItemsAttributes]  WITH CHECK ADD  CONSTRAINT [FK_ItemsAttributes_Items] FOREIGN KEY([ItemID])
REFERENCES [dbo].[Items] ([ID])
GO
ALTER TABLE [dbo].[ItemsAttributes] CHECK CONSTRAINT [FK_ItemsAttributes_Items]
GO

Open in new window

0
 
NaplesFLDaveAuthor Commented:
GREAT help!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now