CODEWITHSUNDEEP
databasedatabase-designrelationship
Cap
Cap

Reputation: 363

An item with multiple categories in DB

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.

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

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

Upvotes: 1

Views: 1718

Answers (1)

Brian
Brian

Reputation: 1248

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

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.

Upvotes: 4

Related Questions