Design a DB tables

Well, for each different CategoryType, why not set up a sub entity / attribute type relationship

So, another table CategoryTypeAttributes will hold the various distinct setting for that CategoryType.

Keyed by CategoryType, you can then have Attributes (as many as needed) with a description of that attribute for display.

You could also add other aspects like some editting rules and such like for each Attribute.

Does that make sense ?

Hmmm..
May you have a sample table to show me?

Sure, give me a few minutes...

Great! ,
But please something that can be for e-commerce site ( like ebay*:}  )

Please try this --

CREATE TABLE Category
(
	 CategoryId INT PRIMARY KEY	 	
	,CategoryName VARCHAR(100)
)
GO

INSERT INTO Category VALUES
(1,'Product'),
(2,'Service')
GO

CREATE TABLE CategoryTypes
(
	 CategoryId INT
	,CategoryTypeId INT PRIMARY KEY	 
	,CategoryType VARCHAR(100)
)
GO

INSERT INTO CategoryTypes VALUES
(1,1,'jewelry'),
(1,2,'Pet'),
(2,3,'ServicesType')
GO

CREATE TABLE Attributes
(
	 AttributeId INT
	,AttributeName VARCHAR(100)
)
GO

INSERT INTO Attributes VALUES 
(1,'ProductCondition'),
(2,'ProductMaterial'),
(3,'Age'),
(4,'Color')
GO

CREATE TABLE Product
(
	 ProductId INT
	,ProductName VARCHAR(100)
	,CategoryTypeId INT 
	,Attributes VARCHAR(100) /*Comma separated AttributeIds*/	
)
GO

INSERT INTO Product VALUES
(1,'Ring',1,'1,2'),
(2,'cat',2,'3,4')
GO

CREATE TABLE ProductAttributeValues
(
	 ProductAttributeValuesID
	,ProductId INT 
	,AttributeId INT
	,AttributeDataType VARCHAR(100)
	,AttributeValue VARCHAR(100)
)
GO

INSERT INTO ProductAttributeValues VALUES
(1,1,1,'string','Product'),
(2,1,2,'string','Silver'),
(3,2,3,'string','1 Year'),
(4,2,4,'string','White')
GO

Select allOpen in new window

Hi,
Well i need more Explinantion about the DB design not a code.

Here is some more code (oops) as an example

CREATE TABLE CategoryType
(
 CategoryID INT identity
,CategoryName VARCHAR(20)
)

CREATE TABLE CategoryTypeAttributes
(
 CategoryAttributeID INT identity
,CategoryID INT
,CategoryAttributeName VARCHAR(20)
)

insert CategoryType (CategoryName) values 
('Ring'),
('Pet')

Insert CategoryTypeAttributes (CategoryID,CategoryAttributeName) values
(1,'Condition'),
(1,'Material'),
(2,'Age'),
(2,'Colour')


Select C.CategoryName,A.CategoryAttributeName
from CategoryType C
Inner join CategoryTypeAttributes A on C.CategoryID = A.categoryID

Select allOpen in new window

Now, when creating the product, you will also need to supply the "answers"  or values to each of those attributes.

So, you will also have to link to the CategoryTypeAttributes when entering Product Details.

Does that help clarify ?

Ok. Please find the details below-

Category -> Master Table , will stored categories. CategoryId is the PK
CategoryTypes -> This will stored Sub Categories ..each sub category will have a category. One category will have many subcateogories, CategoryId is the foriegnKey here. ,CategoryTypesId is the primarykey(PK) here
Attributes -> Master Table, will stored Attributes and their Name....
Product -> This will stored product information  like productId,Name, CategoryTypeId and attributes . CategoryTypeId  is the foriegn key of table CategoryTypes and attributes will store the comma separated keys of Attributes(AttributeId)
ProductAttributeValues -> this table stores the ProductId,AttributeId, its data type and the value of the attribute. This will relate the product and its attribute with the values.

You really need to start with an ER diagram. It will significantly help you in the design stages....

For example, have a look at : https://creately.com/diagram/example/hjs63tgx1/Shopping+cart

Do you have any ER design tools ?

is it possible to show me the solution by a diagram draw like in visual access 2010 design view?

The model is called Entity-Attribute-Value model (EAV).


And you normally have one attribute table per base data type to avoid unnecessary casts. Thus a string table, a number table, a date table.


And to ensure uniquness, you would use a discrinator in the values table with a check constraint:


Sometimes with an intermediate values table for common attributes and for simpler checking for existing values:

drtopserv, you're exactly right when you say:
"I need more Explanation about the DB design not a code."

It's far too early in the design process for "tables" or "code".

You need to do a conceptional data model first (it's easy and quick, but it still helps as a guideline/overview), then second a logical data model.  You can google for details, but this site has a good, brief summary of it:
https://www.1keydata.com/datawarehousing/conceptual-data-model.html

The language of logical modeling is different, but the core terms are:

Entity = someone/something, such as Product, Buyer, Seller, Contract, etc..  [Typically an Entity becomes one or more tables.]
Attribute = one piece of information about an Entity, such as name, weight, date-of-birth, etc.  [Typically an Attribute becomes one or more columns.]
Domain = range of valid values for an attribute; this includes the equivalent of a data type -- date, integer, etc. -- but there should be additional restrictions if they actually apply to the data.  For example, for "order date", the domain might be "date, from current date forward" (unless you will allow back-dated orders).  [Typically Domain becomes a data type and, if applicable, CHECK constraints, which are overlooked way too often on physical dbs.]


Your specific issue could be modeled as:
a supertype and subtypes [that's how I would do this one].  Not as difficult as it sounds.  Just means that a table row can be one -- and only one -- of a list of possible types.
OR
using EAV (because of the complexity involved, this is normally used only if the columns are extremely dynamic)
OR
standard tables with NULL columns as needed.
DON'T DECIDE ON ANYTHING UNTIL YOU'VE SPENT SOME TIME MODELING THE DATA AND ASKING AND ANSWERING QUESTIONS ABOUT IT.

Don't be in too big a rush to assign keys.  Determine entities and as many attributes as possible first.