数据库中具有多个类别的项目 [英] An item with multiple categories in DB

问题描述

我有一个 contacts 表和一个 contact_category 表.我试图在表中反映一个联系人可以属于几个类别.

I have a contacts table and a contact_category table. I am trying to reflect in table that one contact can belong to several categories.

是否有建议的设计模式来实现这一目标?我想到的只是在每个联系人的附加字段中创建一个字符串，并确定该联系人所属的类别.

Is there any recommended design pattern for implementing this? What comes to my mind is just creating a string in an additional field for every contact and concat the categories this contact belongs to.

例如:"cat1，cat3" 表示联系人属于 cat1 和 cat3

For ex.: "cat1,cat3" would mean that a contact belongs to cat1 and cat3

但是，没有任何适当的设计方法吗?

But, isn't there any proper way of designing this?

推荐答案

一般来讲，以我用很少见的观点，用逗号分隔的具有多个值的文本字段在数据库设计中不是一个好主意.

Generally speaking, a comma-delimited text field with multiple values is a bad idea in database design, in my rarely-humble opinion.

我会推荐这样的东西(我在SQL Server中编码，所以我的语法看起来像这样):

I'd recommend something like this (I code in SQL Server, so that's what my syntax will look like):

Contact
    ID    -- primary key
    -- other contact fields
Category
    ID    -- primary key
    -- other category fields

Contact_Category
    Contact_ID    -- foreign key to Contact
    Category_ID   -- foreign key to Category

以上内容使您可以将一个联系人与多个类别关联，并将一个类别与多个联系人关联.如果您有任何问题，请告诉我.

The above allows you to associate a contact to multiple categories and a category to multiple contacts. Let me know if you have any questions.

这篇关于数据库中具有多个类别的项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！