Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>It might help to look at the issue by way of associations. An order <code>has many</code> products, while a product <code>has one</code> order. From there you can discern which fields should be your foreign keys. If you decided to use an ORM (<a href="https://stackoverflow.com/questions/108699/good-php-orm-library">good SO discussion</a>), it will make interacting with the aforementioned associations easier but there is the learning curve associated with the ORM.</p> <p>As far as selecting a size goes, you may want to keep a third "lookup" table with just size information in it. For instance, have a table with <code>id</code> and <code>size</code> where id is the row id and size is "sm", "med", "large", etc. From there, have a product <code>size</code> column that you would have an id to a size. You may also want to have a table that associates an ordered item with an order. So you would have a table with <code>id</code> (of course), <code>order_id</code>, <code>product_id</code>, <code>count</code>, and, if it makes sense to store a size for every item ordered, <code>size</code>. That way you could store the size of each ordered item and know which items in an order were set and which were not with a query like: <code>SELECT product_id from ordered_items WHERE size = '' AND order_id = ?</code>. Assuming MySQL (or SQLite3 I believe), that query would return all ordered items with an order id of ? (whatever your criteria is), the count ordered, and no set size. You would of course want to set the ordered_items <code>size</code> column to be nullable.</p> <p>Sorry for the rambling answer, if any of that is not clear, let me know and I will refine it where necessary.</p> <h3>Update:</h3> <p>While editing my answer, Luke Pittman fleshed out the model/schema for the table I attempted to explain.</p> <h3>Update 2:</h3> <p>In response to your comment below: I have never used sqlite3 on IOS but, from <a href="http://www.techotopia.com/index.php/An_Example_SQLite_based_iOS_5_iPhone_Application" rel="nofollow noreferrer">this post with an example sqlite3 IOS 5 application</a>, it appears that after you set up the database connection, you just issue straight sqlite commands. So the create table command for <code>ordered_items</code> could look something like this:</p> <pre><code>CREATE TABLE ordered_items( id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL, product_id INTEGER NOT NULL, quantity INTEGER DEFAULT 0, size TEXT, price NUMERIC, DEFAULT 0.0, updated DATETIME NOT NULL, created DATETIME DEFAULT current_timestamp FOREIGN KEY(order_id) REFERENCES orders(id) FOREIGN KEY(product_id) REFERENCES products(id) ) </code></pre> <p>Or if you have a size table that has an <code>id</code> and <code>size</code>, you could add another foreign key to the size table by changing the size column to: <code>size_id INTEGER</code> and add the FK with: <code>FOREIGN KEY(size_id) REFERENCES sizes(id)</code>.</p> <h3>Note:</h3> <p>You will want to read up on <a href="http://sqlite.org/foreignkeys.html" rel="nofollow noreferrer">SQLite3 Foreign Key Constraints</a> such as section 4.3 <code>ON UPDATE</code> and <code>ON DELETE</code>. These constraints can help keep your data in sync so that, if for instance you deleted a product but had existing order_items referencing that product_id, it could delete them or set them to some default. You may or may not want that depending on your purposes, but it is a good idea to be familiar with foreign keys and their available constraints.</p>
    singulars
    1. This table or related slice is empty.
    1. POSetup of database for ordersheet
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USEric H
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POSetup of database for ordersheet
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COHi Eric, thanks for your reply. Will you be able to do the model/schema for the table which Luke Pittman fleshed out? I am pretty sure that I am on the same page as you but I just want to be sure. Thanks a lot!
      singulars
      1. PO
      1. USThomas Carlson
    2. CO@ThomasCarlson I misspoke in my initial comment. I added a create table command for ordered_items in my updated answer.
      singulars
      1. PO
      1. USEric H
 

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