Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
Hello All,

I want to design database for my application. In my application users are allowed to create category, subcategory and their attributes. One SubCategory can have more than one attribute of different datatypes. User will select CategoryID,SubCategoryID and add value to its attribute(s). So,I am planning to create different tables for different attributes because i don't want to change main table every time if new attribute created. Two things
i)Create attribute table dynamically
ii)Dynamically DML query in attribute table.
So, relationship will be with CategoryID, SubCategoryID as

MasterCategory
CategoryId SubCategoryID

CategoryAttribute
CategoryID SubcategoryID AttributeValue

So everytime I will writing dynamic queries for insert and create.
Do you think this is fine? If you have any other ideas then let me know.
Posted
Comments
PIEBALDconsult 14-Jul-14 11:26am    
"Do you think this is fine?"

I don't; it sounds like a maintenance nightmare. If you are using Sql Server, have you considered using XML to hold the "attributes"?

What more information can you provide? Any examples of a Category or SubCategory table?

You are trying to badly deviate from relational model. At the same time, all your problems are simply resolved in the relational model. First, let's start from the notion of "sub-category". You can have just one table "Category" with the key "Id". If you add the columns "ParentCategory" which carries the IDs of the categories parent in relation to the category of the current record, you will have the tree of arbitrary depth built on the base of the parent-child relationships between categories.

The rest of it depends on what on what the attribute should be and what "different attributes" may mean. You never need to create a table dynamically. "Different attributes" does not mean different types. So, you can have only one table for all attributes, and they will be different, because the attribute's attributes are different (different values in some or all columns). But let's suppose you have different types of attributes, which means that you have different set of columns for each table. Now, it depends on where those types come from. If the attribute types are known in advance, you need to have a separate attribute table per attribute type, so the problem is reduced to a previous one.

And finally, let's consider the case when you need to create a new attribute type dynamically. It simply means that the type should be represented not as the type of the table, but become a data type. The most basic approach is this: create one new table of dynamic attributes. It can have, say, name, value, and, importantly, a column "owner" with the foreign key pointing to the attribute object. That said, you can have the object "attribute" each having unlimited number of "dynamic attributes".

Please see:
http://en.wikipedia.org/wiki/Relational_model[^],
http://en.wikipedia.org/wiki/Relation_(database)[^],
http://en.wikipedia.org/wiki/Foreign_key[^].

—SA
 
Share this answer
 
Comments
Maciej Los 14-Jul-14 15:24pm    
+5! Valuable answer.
I think OP wants to create databse for hierarchical data. Please, look at my answer.
Sergey Alexandrovich Kryukov 14-Jul-14 15:55pm    
Thank you, Maciej.
Hierarchical data is just the special case of the approach to supporting of some object graphs in relational model I tried to explain...
—SA
Maciej Los 14-Jul-14 17:37pm    
;)
I'd suggest you to read about recursive hierarchies[^].

You should have only 2 tables:
Categories:
CatID INT IDENTITY(1,1)
Parent INT (related to CatID)
CatName NVARCHAR(50)
... (other fields)

and
Category_Attributes:
AttID INT IDENTITY(1,1)
CatID INT (related to CatId in Categories table)


Sample Data:
CatId    Parent    CatName
1        0         MainCat
2        1         MainCatSubCat1
3        1         MainCatSubCat2
4        2         SubCat1SubSubCat1
5        4         SubCat1SubSubCat1SubCat1


Its "visual" representation diagram:
1
|
+---2
|   |
|   +---4
|       |
|       +---5
|
+---3


Do you get it now?
 
Share this answer
 
v2
Comments
Sergey Alexandrovich Kryukov 14-Jul-14 15:56pm    
Useful explanation, my 5.
Look, at the new comment to your comment to my answer though.
—SA
Maciej Los 14-Jul-14 17:37pm    
Thank you, Sergey ;)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900