Databases
StackOverflow2013
Postsdbo
POAdvice on database design for multiple catalogs
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAdvice on database design for multiple catalogs
    primarykey
    data
    text
    <p>I have a relatively simple database design that I will simplify here for the sake of brevity. </p> <p>I have 4 tables => <code>Products, Product_Sizes, Stores, Catalogs</code>. </p> <p>There are 6 stores and each store has it's own unique customized catalog, an assortment of different products and sizes that is chosen from the <code>Products</code> and <code>Product_Sizes</code> tables. </p> <p>I am wondering how to best design the <code>Catalogs</code> table. My ideas are:</p> <pre><code>id store_id product_id product_size 1 1 53522 1 2 1 40299 1 3 2 43326 1 4 2 43326 2 </code></pre> <p>OR</p> <pre><code>id store_id product_id product_sizes 1 1 53522 1 2 1 40299 1 3 2 43326 1,2 </code></pre> <p>Each store has it's own unique (and only one) catalog, so a query to fetch all entries by store_id will result in that store's catalog. </p> <p>Another approach would be to create another table of the combined products and product_size into it's each own unique table, let's call it <code>Products</code>. </p> <pre><code>id product_id product_size 1 1 1 2 1 2 3 1 3 4 1 4 </code></pre> <p>This separate table gives me a unique id for all the possible products and their size variants. This would result in a single id per product for each <code>Catalogs</code> entry.</p> <p>I would love to hear some critique and better suggestions as I feel this just isn't right and can't put my finger on how to better design this. Also, if we were to ever implement more than 1 catalog per store, I know this current design would cause me grief. Any feedback would greatly appreciated!!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USuser2551866
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POAdvice on database design for multiple catalogs
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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