Create tables for Dynamic Product

Question

I want user can create Product category And if it Have SubCategory.define SubCategory.then define column in tables(Brand,Group And Field) Of that Category(or SubCategory).Finally user can add Product Based that Brand,Group And Field column that define before. I Want to create Dynamic Product Database and this my first time for create dynamic tables.I just worry about

1-Correct Creation

2-performance issue(no doubt about types).

I add my diagram Tables and script of tables

CREATE TABLE [dbo].[CategoryBrand](
    [ProductCategoryId] [tinyint] NOT NULL,
    [ProductBrandId] [tinyint] NOT NULL,
 CONSTRAINT [PK_CategoryBrand] PRIMARY KEY CLUSTERED 
(
    [ProductCategoryId] ASC,
    [ProductBrandId] 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].[Group]    Script Date: 6/19/2016 11:15:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Group](
    [Id] [smallint] IDENTITY(1,1) NOT NULL,
    [ProductCategoryId] [tinyint] NOT NULL,
    [GroupName] [nvarchar](250) NOT NULL,
 CONSTRAINT [PK_aaa] 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].[GroupInfo]    Script Date: 6/19/2016 11:15:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GroupInfo](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [GroupId] [smallint] NOT NULL,
    [SubGroupName] [nvarchar](500) NOT NULL,
 CONSTRAINT [PK_GroupInfo] 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].[Product]    Script Date: 6/19/2016 11:15:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Product](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [CategoryId] [tinyint] NOT NULL,
    [Describtion] [nvarchar](max) NULL,
    [ProductBrandId] [tinyint] NOT NULL,
 CONSTRAINT [PK_Product] 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] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[ProductBrand]    Script Date: 6/19/2016 11:15:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ProductBrand](
    [Id] [tinyint] IDENTITY(1,1) NOT NULL,
    [BrandName] [nvarchar](50) NOT NULL,
    [ImageAddress] [nvarchar](500) NULL,
 CONSTRAINT [PK_ProductBrand] 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].[ProductCategory]    Script Date: 6/19/2016 11:15:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ProductCategory](
    [Id] [tinyint] IDENTITY(1,1) NOT NULL,
    [Category] [nvarchar](250) NOT NULL,
    [ParentId] [tinyint] NULL,
 CONSTRAINT [PK_ProductCategory] 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].[ProductField]    Script Date: 6/19/2016 11:15:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ProductField](
    [Id] [smallint] IDENTITY(1,1) NOT NULL,
    [ProductCategoryId] [tinyint] NOT NULL,
    [FieldName] [nvarchar](100) NOT NULL,
    [Describtion] [nvarchar](1000) NULL,
 CONSTRAINT [PK_ProductField] 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].[ProductFieldInfo]    Script Date: 6/19/2016 11:15:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ProductFieldInfo](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ProductId] [int] NOT NULL,
    [ProductFieldId] [smallint] NOT NULL,
    [FieldInfo] [nvarchar](1000) NOT NULL,
 CONSTRAINT [PK_ProductFieldInfo] 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].[ProductGroup]    Script Date: 6/19/2016 11:15:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ProductGroup](
    [ProductId] [int] NOT NULL,
    [GroupIfoId] [int] NOT NULL,
 CONSTRAINT [PK_ProductGroup] PRIMARY KEY CLUSTERED 
(
    [ProductId] ASC,
    [GroupIfoId] 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].[ProductImage]    Script Date: 6/19/2016 11:15:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ProductImage](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ProductId] [int] NOT NULL,
    [FileName] [nvarchar](500) NULL,
    [ImageAddress] [nvarchar](500) NOT NULL,
 CONSTRAINT [PK_ProductImage] 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
ALTER TABLE [dbo].[CategoryBrand]  WITH CHECK ADD  CONSTRAINT [FK_CategoryBrand_ProductBrand] FOREIGN KEY([ProductBrandId])
REFERENCES [dbo].[ProductBrand] ([Id])
GO
ALTER TABLE [dbo].[CategoryBrand] CHECK CONSTRAINT [FK_CategoryBrand_ProductBrand]
GO
ALTER TABLE [dbo].[CategoryBrand]  WITH CHECK ADD  CONSTRAINT [FK_CategoryBrand_ProductCategory] FOREIGN KEY([ProductCategoryId])
REFERENCES [dbo].[ProductCategory] ([Id])
GO
ALTER TABLE [dbo].[CategoryBrand] CHECK CONSTRAINT [FK_CategoryBrand_ProductCategory]
GO
ALTER TABLE [dbo].[Group]  WITH CHECK ADD  CONSTRAINT [FK_aaa_ProductCategory] FOREIGN KEY([ProductCategoryId])
REFERENCES [dbo].[ProductCategory] ([Id])
GO
ALTER TABLE [dbo].[Group] CHECK CONSTRAINT [FK_aaa_ProductCategory]
GO
ALTER TABLE [dbo].[GroupInfo]  WITH CHECK ADD  CONSTRAINT [FK_GroupInfo_aaa] FOREIGN KEY([GroupId])
REFERENCES [dbo].[Group] ([Id])
GO
ALTER TABLE [dbo].[GroupInfo] CHECK CONSTRAINT [FK_GroupInfo_aaa]
GO
ALTER TABLE [dbo].[Product]  WITH CHECK ADD  CONSTRAINT [FK_Product_ProductBrand] FOREIGN KEY([ProductBrandId])
REFERENCES [dbo].[ProductBrand] ([Id])
GO
ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_Product_ProductBrand]
GO
ALTER TABLE [dbo].[Product]  WITH CHECK ADD  CONSTRAINT [FK_Product_ProductCategory] FOREIGN KEY([CategoryId])
REFERENCES [dbo].[ProductCategory] ([Id])
GO
ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_Product_ProductCategory]
GO
ALTER TABLE [dbo].[ProductCategory]  WITH CHECK ADD  CONSTRAINT [FK_ProductCategory_ProductCategory] FOREIGN KEY([ParentId])
REFERENCES [dbo].[ProductCategory] ([Id])
GO
ALTER TABLE [dbo].[ProductCategory] CHECK CONSTRAINT [FK_ProductCategory_ProductCategory]
GO
ALTER TABLE [dbo].[ProductField]  WITH CHECK ADD  CONSTRAINT [FK_ProductField_ProductCategory] FOREIGN KEY([ProductCategoryId])
REFERENCES [dbo].[ProductCategory] ([Id])
GO
ALTER TABLE [dbo].[ProductField] CHECK CONSTRAINT [FK_ProductField_ProductCategory]
GO
ALTER TABLE [dbo].[ProductFieldInfo]  WITH CHECK ADD  CONSTRAINT [FK_ProductFieldInfo_Product] FOREIGN KEY([ProductId])
REFERENCES [dbo].[Product] ([Id])
GO
ALTER TABLE [dbo].[ProductFieldInfo] CHECK CONSTRAINT [FK_ProductFieldInfo_Product]
GO
ALTER TABLE [dbo].[ProductFieldInfo]  WITH CHECK ADD  CONSTRAINT [FK_ProductFieldInfo_ProductField] FOREIGN KEY([ProductFieldId])
REFERENCES [dbo].[ProductField] ([Id])
GO
ALTER TABLE [dbo].[ProductFieldInfo] CHECK CONSTRAINT [FK_ProductFieldInfo_ProductField]
GO
ALTER TABLE [dbo].[ProductGroup]  WITH CHECK ADD  CONSTRAINT [FK_ProductGroup_GroupInfo] FOREIGN KEY([GroupIfoId])
REFERENCES [dbo].[GroupInfo] ([Id])
GO
ALTER TABLE [dbo].[ProductGroup] CHECK CONSTRAINT [FK_ProductGroup_GroupInfo]
GO
ALTER TABLE [dbo].[ProductGroup]  WITH CHECK ADD  CONSTRAINT [FK_ProductGroup_Product] FOREIGN KEY([ProductId])
REFERENCES [dbo].[Product] ([Id])
GO
ALTER TABLE [dbo].[ProductGroup] CHECK CONSTRAINT [FK_ProductGroup_Product]
GO
ALTER TABLE [dbo].[ProductImage]  WITH CHECK ADD  CONSTRAINT [FK_ProductImage_Product] FOREIGN KEY([ProductId])
REFERENCES [dbo].[Product] ([Id])
GO
ALTER TABLE [dbo].[ProductImage] CHECK CONSTRAINT [FK_ProductImage_Product]
GO

I Find My Answer: [http://stackoverflow.com/questions/3241033/designing-database-to-hold-different-metadata-information](http://stackoverflow.com/questions/3241033/designing-database-to-hold-different-metadata-information) — Vahid Akbari, Jun 20 '16 at 10:47

score 1 · Answer 1 · answered Jun 20 '16 at 02:38

1

If it is your first time then make things simple.
Do not use dynamic SQL to create objects.

Before you do your design, review the well known AdventureWorks database. It has similar structure, where you can get some ideas: http://msftdbprodsamples.codeplex.com/releases

If AdventureWorks will not satisfy you, then ask questions on how to modify it for your business needs.

answered Jun 20 '16 at 02:38

Slava Murygin

1,951
1
10
10

tnx I will Check it for Learning More – Vahid Akbari Jun 25 '16 at 10:19

score 0 · Accepted Answer · edited May 23 '17 at 10:29

0

I Find My Answer: this is Database Table that I Want Design with Some Changes:

designing database to hold different metadata information

edited May 23 '17 at 10:29

Community

1
1

answered Jun 23 '16 at 06:49

Vahid Akbari

189
7
25

Create tables for Dynamic Product

2 Answers2