3

All items in the items table have a category that can be derived from the item_category table.

+---------------------------------------------+
| items                                       |
+---------------------------------------------+
| id                 | PK                     |
+--------------------+------------------------+
| item_category_id   | FK(item_categories.id) |
+--------------------+------------------------+

The item_category table references itself. I wanted to create a category->sub-category->sub-sub-category etc. system. I don't know how many nested sub-categories there will be so I thought my best bet would be to enclose that structure in a single table. If the item_category_id is NOT NULL then it has a parent, otherwise it is a super-category and does not have a parent.

+-------------------------------------------+
| item_categories                           |
+-------------------------------------------+
| id               | PK                     |
+------------------+------------------------+
| item_category_id | FK(item_categories.id) |
+------------------+------------------------+

This is where my question lies. The doll_item table is a pivot table. Basically a doll can have many items and an item can belong to many dolls. But there's more to it than that. I want to make sure that for every doll, in the doll_item table, its corresponding item comes from a unique category.

I have tried to pull in the item_category_id for the item in each row; however, I fear that this relation does not enforce that the item_id and item_category_id from the items table necessarily come from the same row. Without this requirement, the addition of the two latter rows in the doll_item table is pointless.

Is it possible to enforce this using MySQL? 

+-------------------------------------------------------+
| doll_item                                             |
+-------------------------------------------------------+
| doll_id              | FK(dolls.id)                   |
+----------------------+--------------------------------+
| item_id              | FK(items.id)                   |
+----------------------+--------------------------------+
| item_category_id     | FK(items.item_category_id)     |
+----------------------+--------------------------------+
| unique(doll_item.doll_id, doll_item.item_category_id) |
+-------------------------------------------------------+

Thanks

RandomSeed
  • 29,301
  • 6
  • 52
  • 87
James Stott
  • 2,954
  • 2
  • 14
  • 15
  • Clarification: You also have an `dolls` table, right? Question: When you say "pivot" table do you mean "many-to-many join" table? Question: Many such join tables have a compound (unique) primary key on the two `id` columns, in your case `(doll_id, item_id)`. Is there some reason you can't do that, and therefore collect your unique `item_category_id` from the `items` table? – O. Jones Dec 28 '14 at 15:16
  • Clarification: Yes, there is a `dolls` table. Answer: Yes, by "pivot" table I do mean that there is a many-to-many join table; this is the `doll_item` table. Answer: I need a more specific constraint than that. I need for each `doll` there to be a unique `item` for each `item_category`. To illustrate the point, lets say I have a 'trousers' item. I don't want to allow a doll to have two different types of trousers on it. This is a dressup game so it will look weird if a doll has two pairs of trousers on. What do you mean "therefore collect your unique `item_category_id` from the `items` table?" – James Stott Dec 28 '14 at 15:52
  • Off-topic but I can't help myself: if you are planning to allow an arbitrary depth of sub-categories, please consider reading [this excellent thread](http://stackoverflow.com/q/192220/1446005). – RandomSeed Dec 28 '14 at 21:55
  • Thanks! Really interesting from what I understand. Although it seems that with adjacency relations nodes can have multiple immediate parents which would be an issue for my architecture. Or have I misunderstood because I saw the implementation of breadcrumbs that is effectively analogous to my problem. – James Stott Dec 29 '14 at 03:45

1 Answers1

3

I want to make sure that for every doll in the doll_item table, [every] corresponding item comes from a [different] category

Use a composite UNIQUE KEY.

I fear that this relation does not enforce that the item_id and item_category_id from the items table necessarily come from the same row

Use a compound FOREIGN KEY.:

CREATE TABLE item (
  id INT NOT NULL,
  item_category_id INT NOT NULL,
  PRIMARY KEY (id),
  INDEX (id, item_category_id) -- see note below
);

CREATE TABLE doll_item (
  doll_id INT NOT NULL,
  item_id INT NOT NULL,
  item_category_id INT NOT NULL,
  PRIMARY KEY (doll_id, item_id),

  UNIQUE KEY (doll_id, item_category_id),

  FOREIGN KEY (item_id, item_category_id)
    REFERENCES item (id, item_category_id)

);

note: in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.

RandomSeed
  • 29,301
  • 6
  • 52
  • 87
  • This looks perfect! Thanks :) ... with regards to your note do you mean that I need a `unique composite key` in my `item` table? Or do you mean that `id` must be the first field before `item_category_id` in the database schema? @randomseed – James Stott Dec 28 '14 at 17:12
  • It only means that the two referenced columns must be covered by an index of any type (regular, unique, primary key, whatnot). The part about them having to be "listed as the *first columns* [in the index definition]" means that an index on `item(id, item_category_id, another_column)` would do the job as well. See also this manual page for a related [discussion on multi-column indexes](http://dev.mysql.com/doc/refman/5.6/en/multiple-column-indexes.html). – RandomSeed Dec 28 '14 at 21:50
  • I see ... thanks a bunch...your answers have been incredibly helpful - I appreciate it a lot! – James Stott Dec 29 '14 at 03:48