动态类别和子类别数据库设计 [英] Dynamic Category and Sub category Database Design

问题描述

Hello All，



我想为我的应用程序设计数据库。在我的应用程序中，允许用户创建类别，子类别及其属性。一个SubCategory可以具有多个不同数据类型的属性。用户将选择CategoryID，SubCategoryID并为其属性添加值。所以，我打算为不同的属性创建不同的表，因为我不希望每次创建新属性时都更改主表。两件事

i）动态创建属性表

ii）属性表中的动态DML查询。

所以，关系将是CategoryID，SubCategoryID为



MasterCategory

CategoryId SubCategoryID



CategoryAttribute

CategoryID子类别ID AttributeValue



所以每次我都会为插入和创建编写动态查询。

你认为这样好吗？如果您有任何其他想法，请告诉我。

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.

推荐答案

您正试图严重偏离关系模型。同时，您的所有问题都可以在关系模型中得到解决。首先，让我们从子类别的概念开始。您可以只使用Id键创建一个类别表。如果添加ParentCategory列，其中包含与当前记录的类别相关的父类别的ID，您将在父子的基础上构建任意深度的树。类别之间的关系。



其余部分取决于属性应该是什么以及不同属性可能意味着什么。您永远不需要动态创建表。 不同的属性并不意味着不同的类型。因此，对于所有属性，您只能有一个表，它们将是不同的，因为属性的属性不同（某些或所有列中的值不同）。但是我们假设您有不同类型的属性，这意味着每个表都有不同的列集。现在，它取决于这些类型的来源。如果事先知道属性类型，则每个属性类型需要一个单独的属性表，因此问题将减少到前一个。



最后，让我们考虑一下您需要动态创建新属性类型的情况。它只是表示类型不应表示为表的类型，而是表示数据类型。最基本的方法是：创建一个新的动态属性表。例如，它可以具有名称，值，并且重要的是具有指向属性对象的外键的列所有者。也就是说，你可以让对象属性各自拥有无限数量的动态属性。



请参阅：

http://en.wikipedia.org/wiki/Relational_model [ ^ ]，

http://en.wikipedia.org/wiki/Relation_（数据库） [ ^ ]，

http：// en .wikipedia.org / wiki / Foreign_key [ ^ ] 。

-SA

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

我建​​议你阅读递归层次结构 [ ^ ]。



你应该只有2张桌子：

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?

这篇关于动态类别和子类别数据库设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！