MYSQL Foreign Key Set

190
December 07, 2017, at 04:09 AM

Abstract: Table A has a row which contains a list of Integers that reference IDs in Table B.

Why not just create a column in table B that references Table A? Because the items in Table B are reusable, and apply to many rows in table A.

Specific example: An educational software, which consists of lessons and line items. Each lesson incorporates many line items, and each line item is used in many lessons.

This many-to-many linking is a common task and I have probably been doing it wrong forever. The easiest way I have done it in the past is to have Table A contain a varchar with a delimited list, interpret that list in code and then run another query to get the needed information from table B.

Is there more elegant way? Some way of combining the functionality of SET data type with Foreign Keys index?

Answer 1

For a Many to Many relationship you will be using 3 tables:

Table Lesson:
Columns: ln_id, ln_lesson_name

Table LineItem
Columns: li_id, li_line_detail

And a relation table LineOfLesson
Columns: ls_lesson_id (references the id in the Lesson table), ls_line_item_id (references the id in the LineItem table)

In this way by querying the three tables you can get either the lessons where a particular line is used or the lines that make up a particular lesson.

Side note: In the relation table the two ids together would be the natural primary key of the table.

Also if there were aditional information regarding the relation it would also be added here for example, you could add when was the line item was added.

Answer 2

What you need is accomplished with a relationship table.

A is one to one with B

  • A or B should contain the id of the other.

A is one to many with B

  • B should contain the id of A.

A is many to many with B

  • Create a relationship table AB that holds ids for both A and B.

These are standard patterns.

READ ALSO
Database: Comparing a Set with a Big Collection of Sets

Database: Comparing a Set with a Big Collection of Sets

How to match a Set with a Big Collection of Sets stored in database[The collection may have millions of Sets]

182
perform MINUS operation in MySQL not working [duplicate]

perform MINUS operation in MySQL not working [duplicate]

This question already has an answer here:

190
MySQL query finding values in a comma separated string

MySQL query finding values in a comma separated string

I have a field COLORS (varchar(50)) in a my table SHIRTS that contains a comma delimited string such as 1,2,5,12,15,Each number representing the available colors

186