CODEWITHSUNDEEP
database-design
brainydexter
brainydexter

Reputation: 20386

How Do I Associate Multiple Records with Each Other?

I have food products that can belong to multiple categories and a category can have many food products. Categories are hierarchical in nature:

I'm trying to design the database schema keeping the two constraints in mind. How should I proceed with the schema design to support this?

As another example, it occurs to me that Stack Overflow has a system similar to this... tags. Each question has multiple tags, and each tag has multiple questions. How is this achieved? How can I adapt this design for my categorization needs?

Upvotes: -1

Views: 480

Answers (2)

David
David

Reputation: 219117

A many-to-many relationship, in most relational database systems, generally consists of two database tables which contain the data in question as well as an intermediate table which links them. Something like this:

Posts
--------
ID
Title
Content

Tags
--------
ID
Title

PostTags
--------
PostID
TagID

This creates a kind of indirect relationship between Posts and Tags through the intermediate table. (Note that the intermediate table can also have an ID column if you really want one, but it might not be needed. Keep in mind also that adding more columns to this table presents the possible risk of creating unintuitive modeling. It's usually not a big deal, but it's something to note as you model your data. Adding more columns to this table can make the "relationship" between the data models a model in and of itself.)

With your food categories, you add an additional layer of complexity in that the categories are hierarchical. However, this can probably be achieved with nothing more than a self-referential column. Something like this:

Products
--------
ID
Name
Description

Categories
--------
ID
Title
ParentCategoryID

ProductCategories
--------
ProductID
CategoryID

The main difference here is that the Categories table now has a column which points back to that table's own ID. So you'd have a Food record in that table with an ID value and, since it's top-level in the hierarchy, a null value for ParentCategoryID. Then the Organic record would have its own ID value and would use the Food record's ID value in its ParentCategoryID field. This creates that hierarchy without adding unnecessary complexity to the data model.

Note that this design by itself doesn't prevent someone from, say, categorizing something as both Food and Organic. If that's not supposed to happen then you'll want to further refine the design and constraints to prevent it. Most of the time that sort of thing is hardened against in the application, but it would be good to do it in the database as well if you can just to get that extra assurance of data integrity.

Upvotes: 1

Petar Minchev
Petar Minchev

Reputation: 47403

This is a many-to-many relationship. A question can have many tags and a tag can have many questions. In the database this is represented with an additional table(tag id and question id).

Upvotes: 3

Related Questions