-1

Let's say I'm designing a catering website where a customer can view a list of items to order from. This list would come from the database, and the table would look like this:

id | name | description               | course |  price  |  cost         
-----------------------------------------------------------------
1  | Pizza| Chicago style, of course! | entree |  20.00  |  10.00

Now let's say I wanted to display allergy information for each item. Since there can be multiple allergens for each item, I'm thinking that a separate table would be better.

For example, if my allergen table looks like this:

id |  allergen     
-------------
1  |  wheat
2  |  eggs
3  |  dairy
4  |  soy
5  |  fish
6  |  nuts

How can I link the two tables to show that the Pizza contains wheat and dairy?

Is having separate tables the correct approach? Looking for suggestions, I've never designed a database before.

  • Possible duplicate of [How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?](https://stackoverflow.com/questions/7296846/how-to-implement-one-to-one-one-to-many-and-many-to-many-relationships-while-de) – Keith Nicholas Mar 13 '19 at 02:50
  • you are probablly getting down voted because of your tittle and your introduction. A question on SO really needs to have a question in the title, sometimes thats tricky as it can be hard to know what words to use when you are new to a topic. But it is never appropriate to use the title you are currently using, people will rush in to downvote / close your question. Your question is fine, even if it is a duplicate – Keith Nicholas Mar 13 '19 at 02:54
  • The downvote could be because (see the downvote arrow mouseover text) it shows not research effort. There have been close votes for duplication & broadness. Before considering posting please always google many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. Sufficient effort includes following a published academic information modeling & database design textbook. [ask] – philipxy Mar 13 '19 at 04:04

2 Answers2

1

This would be a many to many relationship between items table and Allergen table (as per your example). In this case, there should a mapping/junction table which will map the items with allergen.

Let say the mapping table is items_allergen. The table will look like this:

item_id |  allergen_id
----------------------
     1  |  1
     1  |  3

You would need to join the tables to retrieve desired columns.

AB_SH
  • 11
  • 3
1

The relational model does not "link" tables for querying. Tables represent relationships. Queries return tables/relationships in terms of other ones. They do this via relation & logic operators combining tables. The result table's relationship is a corresponding combination of the tables' relationships. Read about relational database querying. You need to add a "join" or "association" table giving item & allergen id pairs where the item contains the allergen. Then CROSS JOIN of your 3 tables WHERE Items id = Pairs item id & Allergens id = Pairs allergen id gives rows where the name's item contains the allergen.

FKs (foreign keys) get called "links" & (wrongly) "relationships" but they aren't part of querying.

Find & follow a published academic information modeling & database design textbook.

philipxy
  • 14,867
  • 6
  • 39
  • 83