mysql 表映射_MySQL：了解映射表

When building a category navigation system for a business directory with a many to many relationship, I understand that it is good practise to create a mapping table.

Category Table ( CategoryId, CategoryName )

Business Table ( BusinessId, BusinessName )

Category Mapping Table ( BusinessId, CategoryId )

When I join the Category table and Business table to create the mapping table would this then give me a table which contains every possible business and category relationship?

I have 800 categories and 1000 business listings. Would that then give me a table containing 800,000 possible relationships. If so how would I focus on only the relationships that exist? Would I have to go through all listings (800,000) marking them as true or false?

I have been getting really confused about this so any help would be much appreciated.

解决方案

When using many-to-many relationships, the only realistic way to handle this is with a mapping table.

Lets say we have a school with teachers and students, a student can have multiple teachers and visa versa.

So we make 3 tables

student

id unsigned integer auto_increment primary key

name varchar

teacher

id unsigned integer auto_increment primary key

name varchar

link_st

student_id integer not null

teacher_id integer not null

primary key (student_id, teacher_id)

The student table will have 1000 records

The teacher table will have 20 records

The link_st table will have as many records as there are links (NOT 20x1000, but only for the actual links).

Selection

You select e.g. students per teacher using:

SELECT s.name, t.name

FROM student

INNER JOIN link_st l ON (l.student_id = s.id)

INNER JOIN teacher t ON (l.teacher_id = t.id)

ORDER BY t.id, s.id

Normally you should always use an inner join here.

Making a link

When you assign a teacher to a student (or visa versa, that's the same).

You only need to do:

INSERT INTO link_st (student_id, teacher_id)

SELECT s.id, t.id

FROM student s

INNER JOIN teacher t ON (t.name = 'Jones')

WHERE s.name = 'kiddo'

This is a bit of a misuse of an inner join, but it works as long as the names are unique.

If you know the id's, you can just insert those directly of course.

If the names are not unique this will be a fail and should not be used.

How to avoid duplicate links

It's very important to avoid duplicate links, all sorts of bad things will happen if you have those.

If you want to prevent inserting duplicate links to your link table, you can declare a unique index on the link (recommended)

ALTER TABLE link_st

ADD UNIQUE INDEX s_t (student_id, teacher_id);

Or you can do the check in the insert statement (not really recommended, but it works).

INSERT INTO link_st (student_id, teacher_id)

SELECT s.id, t.id

FROM student s

INNER JOIN teacher t ON (t.id = 548)

LEFT JOIN link_st l ON (l.student_id = s.id AND l.teacher_id = t.id)

WHERE (s.id = 785) AND (l.id IS NULL)

This will only select 548, 785 if that data is not already in the link_st table, and will return nothing if that data is in link_st already. So it will refuse to insert duplicate values.

If you have a table schools, it depends if a student can be enrolled in multiple schools (unlikely, but lets assume) and teachers can be enrolled in multiple schools. Very possible.

table school

id unsigned integer auto_increment primary key

name varchar

table school_members

id id unsigned integer auto_increment primary key

school_id integer not null

member_id integer not null

is_student boolean not null

You can list all students in a school like so:

SELECT s.name

FROM school i

INNER JOIN school_members m ON (i.id = m.school_id)

INNER JOIN student s ON (s.id = m.member_id AND m.is_student = true)