1

I have 3 SQL tables: App, Marketplace, and Category.

These entities have the following relationships:

  • A marketplace has many apps. (one to many)
  • A marketplace has many categories. (one to many)
  • A app has many categories.
  • A category belongs to many apps. (many to many)

I came up with this:

Diagram

This almost works, but the problem is that I can create a app from a marketplace that has categories that don't belong to that marketplace. I'd like to enforce that if it's possible and haven't been able to come up with a solution.

Community
  • 1
  • 1
Billzabob
  • 33
  • 1
  • 3
  • Unfortunately, MySQL is very limited in the kinds of constraints it can enforce automatically. The only built-in constraints it has is unique keys and foreign keys. You can do this kind of check in a trigger, but it's often easier to do it in the application logic than the database. – Barmar Oct 26 '18 at 22:41
  • @Barmar Thanks for the info. – Billzabob Oct 26 '18 at 22:47
  • I think you have some issues here. You need to be specific about "marketplace has many categories" and "an app has many categories". The word category is not the same in both sentences (at least in the real world). For example an application can be a "web application" and is sold in a market place under "business tools". A more accurate way maybe to say that a marketplace has groups of applications and each application is classified under several categories/types. Further details exist, I could elaborate if you want to know more. – NoChance Oct 27 '18 at 00:09
  • Don't you need a marketplace_category table? – Strawberry Oct 27 '18 at 09:45
  • @NoChance For my scenario these categories are the same and they are what they're sold in the market as. An app's category is the category it's listed under in the marketplace. – Billzabob Oct 27 '18 at 23:40
  • Possible duplicate of [What are the options for storing hierarchical data in a relational database?](https://stackoverflow.com/q/4048151/3404097) – philipxy Oct 27 '18 at 23:54
  • Sorry, posted the wrong link. PS This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on [mcve]. – philipxy Oct 27 '18 at 23:54
  • Without separating the category as per my comment above, you may not get a good design. You can solve the problem you have mentioned by using a composite foreign key such as MarketPlaceID+AppCategoryID as a one Foreign Key of the many-to-many table and MarketPlaceID+AppID for the other foreign key. However, this is not looking good because of what I mentioned before. – NoChance Oct 28 '18 at 03:59

0 Answers0