How to improve the fields name

I am trying to improve the fields name to be more obvious what it means.. is the fields name ok? or what can be changed to make it more obvious?

mysql> select * from order_items;
+----+----------+--------+----------+------------+-------+
| id | order_id | name   | quantity | item_price | total |
+----+----------+--------+----------+------------+-------+
|  1 |        5 | Item 1 |        2 |       3.00 |  6.00 |
|  2 |        5 | Item 2 |        1 |       2.00 |  2.00 |
+----+----------+--------+----------+------------+-------+

mysql> select * from orders;
+----+---------+---------+-----------------+---------------+----------------+----------+---------------开发者_运维知识库+------------+----------------+------------+----------------------+--------------------+--------------------+----------------------+
| id | shop_id | user_id | shipping_method | shipping_fees | payment_method | card_fee | commision_fee | item_total | customer_total | shop_total | shop_total_remaining | our_commission_net | our_commission_vat | our_commission_gross |
+----+---------+---------+-----------------+---------------+----------------+----------+---------------+------------+----------------+------------+----------------------+--------------------+--------------------+----------------------+
|  5 |      29 |       9 | delivery        |          1.00 | card           |     0.50 |            13 |       8.00 |           9.50 |       9.00 |                 7.83 |               1.17 |               0.23 |                 1.40 |
+----+---------+---------+-----------------+---------------+----------------+----------+---------------+------------+----------------+------------+----------------------+--------------------+--------------------+----------------------+

Fields name description:

• item_total Total cost from order_items.order_id = 5

• customer_total Total cost for customer to pay (item_total + card_fee + shipping_fee)

• shop_total Total order for the shop (item_total + shipping_fees)

• shop_total_remaining Total remaining to pay back to shop (shop_total - our_commission_net)

• our_commission_net Commission I will make (commission_fee * shop_total / 100)

• our_commission_gross Commission inc VAT (our_commission_net + our_commission_vat)

Did I add unnecessary fields?

I used PHP to calculate the cost.

I can see you've actually added many fields that where not necessary. Think of it this way: If you can explain how to calculate a field by applying any operations to other fields, then it is a calculated field.

Those fields shouldn't be part of a minimalistic design as they can be obtained by operating over other fields. Let's go for the most complex example in your tables.

If you wanted to get the total of all the items for an order in your example you would just select the item_total field. But what happens if it wasn't there? Could you still get that number? Yes. This is how:

select sum(oi.total) from order_items oi
inner join order o on (oi.order_id = o.id)
where (o.id = 5)

Now, we've got rid of one field, but can we remove the order_items.total field and still get this result? Yes. Because it is also a calculated field. This is how:

select sum(oi.quantity * oi.item_price) from order_items oi
inner join order o on (oi.order_id = o.id)
where (o.id = 5)

Applying a similar pattern you can get rid of all the fields you've mentioned. And then you'll have a minimal design.

One thing that worths mentioning is that calculating fields is more complex than just querying the value so they are more expensive in terms of CPU and HD usage. The advantage of calculated fields is that you avoid data redundancy and save a bit of space too :)