Order or invoice details table normalization

[Robert_SF]

A lot of times the rows that make up the body of an "order" are not column compatible. Even in the case of an ordinary grocery tab, you have things sold by weight and things sold by the piece. So you need a column for "weight" and a column for "$/weight" when you ring up a half-pound of hamburger, but you don't when you ring up the package of hamburger buns. And if you start including payments, discounts, and other things, the incompatibility of the columns only grows.

How do you deal with that? Do you follow normalization rules and wind up with separate tables for groceries, meat and produce, payments, discounts, etc? And do you then join them so they can be displayed in a single chronological list?

Or is this a valid case for denormalizing, so you put everything in one table and deal with the column differences programmatically?

Thanks for any input!

 

[jmcilhinney]

It's pretty simple. You have a unit and a price per unit. That's it. The final price is the price per unit multiplied by the number/amount of units. If the unit is "item" and the number of items sold is 3 then the final price is 3 times the price per unit. If the unit is "kg" and the amount is 1.2 kg then the final price is 1.2 times the price per unit.

 

[Robert_SF]

Well, indeed, that's quite true.

Of course, there's still the issue of payments, discounts, and any situation where the columns really are quite different, too different. After some research, this is what I came up with, in case it helps someone. Took me a while to find it, but generally, you solve it with multiple tables, as explained here: mysql - DB design : save different details of payment (credit or check) - Stack Overflow and seconded here SQL/MySQL structure (Denormalize or keep relational) - Stack Overflow.