In the real world, any order would have the above fields, but I'm unsure if it is a mortal sin to store calculated values in the table?
I understand the downside of this, but here it seems more practical to be able to easily reference these values in one table versus having to do fancy queries and what not.
The downside is you have to maintain them in two places now. Have you considered creating a view that generates those values? You could write the logic to compute them once and just reuse it.
No, mostly not a sin at all. Order headers almost invariably directly contain the order total without having to re-calculate it.
I do have some concerns though, in rough order of importance, most to least:
payment_amount should definitely be stored in a separate table, since there always can be many payments to one order
I don't see why you'd need an id and an order_no: isn't the order_no unique already?
If the order_total is simply a sum of other values in the same row, then it should be a computed column, not a column with a data type. That is, like this : order_total AS order_subtotal + order_shipping + order_sales_tax rather than order_total decimal(...,...)
I have been away from doing database design and development for some time and trying to get my head back into things. Am working on my data model currently.
I can share more if you'd like, since the above is just a snippet of the table.
Valid point, but for right now I am only offering one form of payment.
The id is autogenerated and I was thinking of maybe having a more user-friendly order_no too.
For instance, wouldn't it look weird to have "Order Number" = "1"?!
Maybe I should share more of what I have in my data model?
Data types would help. I'll assume id is an int data type. I'll also assume SQL Server as the dbms but these points are valid for other dbmses as well. [My first major programming job was in an Order Entry, Shipping and Credits system, so I'm very familiar with related data structures.]
If payment is just the main method that customer uses to pay, it should be in the CUST table. If it's payment(s) for a specific order, it/they should be in an ORDER_PAYMENTS table. There can be multiple payments and/or credits/adjustments, etc., for a single order.
The ORDER_DETAILS key should not be just ( id ) but rather ( order_id, id ). This will cluster the details rows by parent key first and the SQL engine will "know" they are clustered that way. ( * )
Personally I think street addresses should always be stored in a separate table, for efficiency and for ease of scrubbing the data. Thus, the ORDERS table would have a shipping_address_id rather than the entire address. Since a given customer tends to ship to the same address over and over, this can cut down a lot on storage, while making data easier to scrub. This approach also makes it easy to pull up all the addresses used by a single customer.
( * ) I'm a DBA, trust me on this one. Best clustering will allow an extremely efficient MERGE join between the tables, even if a lot of orders are being joined. Btw, the "default" approach of automatically clustering every table on an id column is terrible overall for performance. It's the most damaging myth in physical db design. The clustering key is the single most important factor for table performance. Thus, it should always be chosen based on specific table structure and usage, not on an extremely generic (supposed) "rule".
I would be using the autoincrement feature and the datatype would be some type of int
(This will be done in mySQL actually - don't hate me)
Then you're the person to talk to for help!
The payment_amount would pertain to an order and not a customer. I could create an ORDER --||------|<-- ORDER_PAYMENTS
I think you misunderstand what I typed.
I was taught that every table has an "id" and that it uses auto-increment.
Then I also picked up that on a "junction" table, you use the primary keys from the parent tables to form a second unique key that is a composite (?) key.
ORDER_DETAIL
id (pk)
order_id (uk-part1)(fk)
product_id (uk-part2)(fk)
So I believe I have what you were describing above...
Yes, I could have an ADDRESS table as well.
Not sure what a MERGE join is...
Didn't completely follow you on that last paragraph.
The auto-increment part is OK, as long as you do not make it a Primary Key (PK) by default, because in MySQL that makes it the clustering key.
The single most-damaging myth in physical db design is that auto-increment should always alone be the PK. The clustering key, which will be the PK for MySQL, is the single most important factor for table performance. Thus, it should always be chosen based on specific table structure and usage, not on an extremely generic (supposed) "rule".
For your ORDER_PRODUCTS table, you should insure that the combination of ( order_id, id ) is the primary key. Id can still be an auto-increment column, just be careful not to declare that as a PK, since by definition a table can have only one PK.
For MySQL, the CREATE TABLE will look roughly like this:
CREATE TABLE ORDER_PRODUCTS (
order_item_id ... NOT NULL AUTO_INCREMENT,
order_id ... NOT NULL,
...other_columns,some may be NULL...,
CONSTRAINT ORDER_PRODUCTS__PK PRIMARY KEY ( order_id, order_item_id )
) Engine=InnoDB;
