Sql design problem - items in multiple sections

Question

I've got a sections table, and an items table.

The problem is each item may be in one or more sections, so a simple 'section_id' for each item won't work, and sql doesn't have a way to store arrays where I can do say "WHERE 5 in section_ids"...

I've considered storing the list of ids as a comma separated string, the problem is I see no way to then check if an item is in a given section from the sql query. The only option I see here is to select the entire table, and parse the strings in php. Needless to say that with 1000's of items this isn't a good idea.

Is there a better way to 'link' an item with multiple sections, and be able to easily select all items for a given section id?

Answer 1

You need a third table itemsPerSection with a primary key composed of both itemid and sectionid, this way you can have a N to N relationship and it's very easy to search on.

So:

Items   -   ItemsPerSection   -   Secion
itemid  <->   itemid
             sectionid        <->   sectionid

Answer 2

You need a third table, called a junction table, that provides the N to N relationship with 2 foreign keys pointing at the parent tables.

Answer 3

In order to represent a many-to-many relationship, you need a support table with SectionId and ItemId. Both should be foreign keys to their respective tables and the primary key of this table should be both columns.

From Wikipedia:

Because most DBMSs only support one-to-many relationships, it is necessary to implement such relationships physically via a third junction table, say, AB with two one-to-many relationships A -> AB and B -> AB. In this case the logical primary key for AB is formed from the two foreign keys (i.e. copies of the primary keys of A and B).

Answer 4

The way I know (but I am not a seasoned database designer!), and that I saw in several databases, is to have a third table: it has two columns, one with IDs of the sections table, one with the IDs of the items table.
It creates a relation between these entries without much cost, allowing fast search if you make a compound index out of both IDs.

Answer 5

You need an intermediate lookup table:

CREATE TABLE item_in_section (item_id int, section_id int)

(I'm guessing about your key types, use whatever ones are appropriate).

To find items in a section:

SELECT item.* from item, item_in_section WHERE item_in_section.item_id = item.item_id AND item_in_section.section_id = X GROUP BY item_id

To find sections an item belongs to

SELECT section.* from section, item_in_section WHERE item_in_section.section_id = section.section_id AND item_in_section.item_id = Y GROUP BY section_id

Answer 6

You're talking about a many-to-many relationship. In normalized form that's best handled with a third table:

items
sections
itemsections

Each row in itemsections has an item id and a section id. For normal one-to-many relationships, that's not needed but it's standard practice for what you're looking at.

Answer 7

You need an intersection table to sit between the two, ie a table that describes which items are in which sections.

Something like..

CREATE TABLE item_sections (
  ID datatype
  ITEM_ID datatype,
  SECTION_ID datatype);

You'll then need to join the tables to get the data out...

SELECT items.*
FROM   items, item_sections
WHERE  items.id = item_sections.item_id
and    item_sections.section_id = the-id-of the-section-you-want

Answer 8

You need to store the section relationship in a second table. Here's a really simple example:

CREATE TABLE foos (
    id              INTEGER,
    name            VARCHAR
)

CREATE TABLE foo_sections (
    foo_id              INTEGER,
    section_name        VARCHAR,
)

-- Add some 'foos'
INSERT INTO foos (1, 'Something');
INSERT INTO foos (2, 'Something Else');

-- Add some sections for each 'foo'
INSERT INTO foo_sections (1, 'Section One');
INSERT INTO foo_sections (1, 'Section Two');
INSERT INTO foo_sections (2, 'Section One');

-- To get all the section names for a specific 'foo' record:
SELECT section_name FROM foo_sections WHERE foo_id = 1
> Section One
> Section Two

Of course in the second table you could store a reference to a third 'sections' table, but I excluded that for clarity.

Good luck :)

Answer 9

You could store several IDs in a field, separated by a comma and then use the FIND_IN_SET command:

SELECT * FROM items WHERE FIND_IN_SET(5, section_id);

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set