StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Without a detailed description of the problems you've run into, it's hard to say how to fix them. It should be possible to implement what you want with the described design. However, you seem to be misunderstanding relational databases, which is why your database architecture is eyebrow-raising.</p> <p>A database table is not a spreadsheet. While a table can have millions of rows, the number of columns is usually limited to a much smaller number. Even when the limit is not a problem, the way relational databases are designed does not lend itself well to storage of uniform data across a large number of columns. For example, your database won't be able to answer questions such as “what's the best-selling product”, “what products have been purchased by more than 100 customers”, etc.</p> <p>The rule of thumb is that each table should contain a fixed number of columns. If you find that you'll have to add columns when you e.g. start selling new products, you're doing it wrong.</p> <p>Here's what you should probably do in your application:</p> <ul> <li><code>products</code> should be a table with one row per available product, containing, for each product, a unique <code>id</code>, the price and other information such as name, manufacturer etc.</li> <li><code>purchases</code> should contain one row for every combination of (customer, product) where a customer has ordered the product. The row shouuld contain the customer id, the product id, and quantity ordered.</li> </ul> <p>Using SQL, you can then easily construct a query that produces the set of products ordered by a specific customer. Using a join, this information can be enhanced with product names and prices.</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