StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>So, you have a hierarchy in the categories, yes? Is it one level (category and child category) or any number (children can have children, etc)? That will impact what the solution is. </p> <p>Generally, you'd model something like this with a many-to-many relationship, like:</p> <pre><code>CREATE TABLE Item( item_id INT NOT NULL, item_name VARCHAR(255) NOT NULL ) CREATE TABLE Category( category_id INT NOT NULL, category_name VARCHAR(255) NOT NULL ) CREATE TABLE Item_Category( item_id INT NOT NULL REFERENCES Item(item_id), category_id INT NOT NULL REFERENCES Category(category_id) ) </code></pre> <p>A record in "Item_Category" means that the listed item is in the listed category. You can then do joins between 2 (or 3) of those tables to list which items are in which categories:</p> <pre><code>SELECT I.* FROM Item I INNER JOIN Item_Category IC ON I.item_id = IC.item_id INNER JOIN Category C on IC.category_id = C.category_id WHERE C.category_name = 'MyCategory' </code></pre> <p>Or which categories an item has:</p> <pre><code>SELECT C.* FROM Category C INNER JOIN Item_Category IC.category_id = C.category_id INNER JOIN Item I on IC.item_id = I.item_id WHERE I.item_name = 'MyItem' </code></pre> <p>If there's a hierarchy in the categories, that could be expressed using a recursive relationship in the category table, such as:</p> <pre><code>CREATE TABLE Category( category_id INT NOT NULL, category_name VARCHAR(255) NOT NULL, parent_category_id INT NOT NULL REFERENCES Category(category_id) ) </code></pre> <p>That makes things more complicated because you have to use recursive queries to get all the records for a category and its children. A simpler way, if you have only two levels of categories, it to simply make it a second field on the category table, like:</p> <pre><code>CREATE TABLE Category( category_id INT NOT NULL, category_name VARCHAR(255) NOT NULL, subcategory_name VARCHAR(255) NULL ) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload