StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><code>item</code> table (contains the items)</p> <pre><code>item_id name </code></pre> <hr> <p><code>options</code> table (contains all options)</p> <pre><code>option_id name type -- color, front_color, back_color, size, shoe_size etc. </code></pre> <hr> <p><code>option_value</code> table (stores all available values per option)</p> <pre><code>option_value_id option_id value </code></pre> <hr> <p><code>item_available_option</code> (stores all available options per item)</p> <pre><code>item_id option_id </code></pre> <hr> <p><code>item_available_option_value</code> (stores all available option values per item per option)</p> <pre><code>item_id option_id -- not required, but I added since it's easier to figure it out option_value_id </code></pre> <hr> <p><code>orders</code> table (stores the orders)</p> <pre><code>order_id customer_id order_date billing_address delivery_address </code></pre> <hr> <p><code>order_position</code> table (contains the order positions)</p> <pre><code>order_pos_id order_id item_id quantity </code></pre> <hr> <p><code>order_pos_option</code> table (contains the options for each order position)</p> <pre><code>order_pos_id option_id option_value_id </code></pre> <p>This is quite a generic approach that allows for an undefined number of options and option value that are defined on a per item basis.</p> <p>If there are not that many options, an alternative would be to go specific, something like this:</p> <hr> <p><code>colors</code> table (contains all colors)</p> <pre><code>color_id name </code></pre> <hr> <p><code>item_available_colors</code> (available colors per item_id)</p> <pre><code>item_id color_id </code></pre> <hr> <p><code>sizes</code> table (contains all sizes)</p> <pre><code>size_id name </code></pre> <hr> <p><code>item_available_sizes</code> (available sizes per item_id)</p> <pre><code>item_id size_id </code></pre> <hr> <p><code>order_position</code> table (contains the order positions)</p> <pre><code>order_pos_id order_id item_id quantity color_id size_id </code></pre> <p>The <code>item</code> and <code>orders</code> tables stay the same, all others are not needed anymore.</p> <p>There are lots of other possible variations, this is meant to provide you with a starting point.</p>

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