Setup of database for ordersheet

Question

I am working on a iPad ordering web app at the moment which consists of different pieces of clothing. The linesheet will be built like this:

20-30 different product will be shown in the same form. Every product have five different sizes to choose from and every size has a different quantity depending on what the buyer would like to order.

I have a products table with the following columns:

id
product_number  
product_name    
product_size    
product_color   
product_description     
product_image   
product_title   
product_category    
product_composition     
product_price_wholesale_dkk     
product_price_wholesale_eur     
product_price_retail_dkk    
product_price_retail_eur    
product_active  
product_detail_one  
product_detail_two  
product_detail_three    
product_xsmall  (activate size for this product? is it avail.)
product_small (activate size for this product? is it avail.)
product_medium (activate size for this product? is it avail.)
product_large (activate size for this product? is it avail.)
product_xlarge (activate size for this product? is it avail.)

and an orders table with the following columns:

id 
order_store 
order_reference 
order_mail 
order_payment_details 
order_products 
order_value_total_wholesale_dkk 
order_value_total_retail_dkk 
order_value_total_wholesale_eur 
order_value_total_retail_eur 
order_date 
order_phone 
order_VAT 
order_address 
order_comments 
order_product_numbers 
order_product_pieces 
order_store_country

My problem is that I can't figure out how the database should be set up. When the order confirmation is sent to the buyer every product should be listed with the specific number of sizes for every product, and since every size might or might not be selected it needs to identify which ones has been chosen and how many of the particular size for the particular product.

How will I be able to make the system check first of all which sizes for the specific product has been ordered and then next how many of that size (database wise) and then PHP wise when choosing the tables in my script?

Thanks in advance.

Your design will not stand the test of time. If you want to do this for the long term, you should definitely look at Hay's or Silverston's patterns books. They are both on Safari, which is 12 bux for a month — Neil McGuigan, Jun 18 '12 at 18:52

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

It might help to look at the issue by way of associations. An order has many products, while a product has one order. From there you can discern which fields should be your foreign keys. If you decided to use an ORM (good SO discussion), it will make interacting with the aforementioned associations easier but there is the learning curve associated with the ORM.

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 id and size where id is the row id and size is "sm", "med", "large", etc. From there, have a product size 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 id (of course), order_id, product_id, count, and, if it makes sense to store a size for every item ordered, size. 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: SELECT product_id from ordered_items WHERE size = '' AND order_id = ?. 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 size column to be nullable.

Sorry for the rambling answer, if any of that is not clear, let me know and I will refine it where necessary.

Update:

While editing my answer, Luke Pittman fleshed out the model/schema for the table I attempted to explain.

Update 2:

In response to your comment below: I have never used sqlite3 on IOS but, from this post with an example sqlite3 IOS 5 application, it appears that after you set up the database connection, you just issue straight sqlite commands. So the create table command for ordered_items could look something like this:

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)
)

Or if you have a size table that has an id and size, you could add another foreign key to the size table by changing the size column to: size_id INTEGER and add the FK with: FOREIGN KEY(size_id) REFERENCES sizes(id).

Note:

You will want to read up on SQLite3 Foreign Key Constraints such as section 4.3 ON UPDATE and ON DELETE. 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.

Hi 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! — Thomas Carlson, Jun 19 '12 at 08:40
@ThomasCarlson I misspoke in my initial comment. I added a create table command for ordered_items in my updated answer. — Eric H, Jun 19 '12 at 13:38

score 1 · Answer 2 · answered Jun 18 '12 at 18:46

I am not sure if I completely understand your question, however from my experience it would be best to have multiple tables just for ordering. For example you already have an "orders" table - I would add another table "order_items" and link them by an association column, say "order_id". So theoretically your "order_items" table could have the following layout:

order_item_id
order_id
product_id
ordered_size
ordered_quantity
ordered_price
...

Does this help/make sense?

score 1 · Answer 3 · answered Jun 18 '12 at 18:58

Uhm, start by thinking that each size makes a different product, you don't need to store every size/color/model in each product. If you set up a products_table like this:

id
product_number  
product_name    
product_size    
product_color   
product_description 
..
product_group
product_amount_available

you will have two different records for the same t-shirt with different colour (for example). When a buyer buys seomthing, it will need to choose the product, as well as the size he needs and the colour he likes. I just added (but don't know if you need it) the 'product_amount_available' field, that tracks the amount of that item you have (so you could say "this product is not available", and the 'product_group' field, which keeps track of all the similar items (the ones which only vary by size, colour, or whatever you think)

The order table looks ok.

When it gets to the system, it will receive the order, check the id's and retrieve info from the products table (maybe decreasing the value of 'product_amount_available', to reflect that some of that prodct have been bought), and then creates a list with the items bought in that order, with a query that looks like this:

select * from products where id in (1, 2, 3, 5, 7)

where the numbers are the ID's of the products given in the order.

Speaking of the user interface, you could just show one of the same group, so that the user will know that there are variations on that very item. You could do this using a 'select distinct' query, or by some PHP programming.

I'm not sure this is what you wanted to know, hope it helps anyway

Setup of database for ordersheet

3 Answers3

Update:

Update 2:

Note: