Reputation: 20386
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
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
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