Welcome to the most active Linux Forum on the web.
Go Back > Forums > Non-*NIX Forums > Programming
User Name
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.


  Search this Thread
Old 09-22-2017, 12:30 PM   #1
Registered: Jan 2017
Posts: 32

Rep: Reputation: Disabled
Conceptual SQL-Question - price per piece and price per weight

Hi there.

So I'm currently setting up a database and I am unsure how to go about this:
there will be buying transactions listed in the database, as well as the related product information.
Some products have a price per piece, some have a price per weight and the transaction should contain for each product the information either how many or how much of the product was bought.

Now currently the table for product-information lists just "price" and the table for a position on the transaction features "amount".
Now, what would be more sensible:
1. Listing the price for something bought by weight in like "price per tons" and then have the transaction position have the appropriate value in tons - which would probably mean I have to allow decimal values for amounts, which I think is a horrific solution
2. have a column in product-information which lists whether the product is bought by piece or by weight and then figure the rest out via the interfacing program used by the user?
or, preferably 3. the simple and obvious conceptual solution I have not come up with.
Old 09-22-2017, 01:11 PM   #2
Registered: Oct 2008
Distribution: Slackware [64]-X.{0|1|2|37|-current} ::12<=X<=14, FreeBSD_10{.0|.1|.2}
Posts: 4,715
Blog Entries: 6

Rep: Reputation: 2575Reputation: 2575Reputation: 2575Reputation: 2575Reputation: 2575Reputation: 2575Reputation: 2575Reputation: 2575Reputation: 2575Reputation: 2575Reputation: 2575
Your description of the tables seems to be incomplete, it does not tell us how the product information captures the price unit (i.e. per weight or per piece).

Why would you think that providing for decimal values for amounts would be horrific? If the quantity can be fractional (i.e. 1/2 ton) then that is the meaning that the data model must capture.

That said, you seem to be confusing the meaning of the data with the presentation of the data. The database should capture the meaning, price, price unit and quantity in this case. The presentation should communicate the meaning of the data in whatever manner is most helpful to the end user.

If the price unit is per weight then transaction data should capture that meaning and the presentation should clearly state "Price per weight unit", and the total should be derived from that relationship. If it is price per item then it should say that instead.

You can perform the calculation and formatting in the queries or in the scripting code, my preference is to do as much as possible in the query and limit the scripting to style alone.

Last edited by astrogeek; 09-22-2017 at 01:20 PM. Reason: typos
1 members found this post helpful.
Old 09-22-2017, 01:18 PM   #3
Registered: Aug 2007
Location: Zurich, Switzerland
Distribution: Gentoo
Posts: 986

Rep: Reputation: 126Reputation: 126
"product-information" definitely needs the additional attribute of "unit" - if you don't integrate it you'll run into a lot of problems at least later when e.g. trying to generate statistical information.
I agree with astrogeek's statement about clearly splitting the different layers of the app.
Old 09-22-2017, 01:23 PM   #4
Registered: Jan 2017
Posts: 32

Original Poster
Rep: Reputation: Disabled
Well, so far I haven't captured the unit of measurement in the product info.

Thought it would be horrific because it means a little work on restricting possible insertion values in the user interface. So, no good reason actually.

So I would add unit of measurement to the product info and allow for decimal values as quantities in the transaction info, and address the rest of my concerns as a matter of presentation.

Thanks folks.

Last edited by MrMeeSeeks; 09-22-2017 at 01:25 PM.


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Question about price circus78 Red Hat 6 01-15-2015 10:25 AM
Processor Upgrade (Pentium M) VS. Price: Which has the best price/tech ratio? hanzj Linux - Laptop and Netbook 8 03-22-2005 06:18 PM
Processor Upgrade (Pentium M) VS. Price: Which has the best price/tech ratio? hanzj General 3 03-21-2005 01:10 AM
Price of a PC FrostBot General 2 09-14-2004 10:51 PM
price class_struggle Linare 1 08-26-2004 10:27 PM > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 01:47 PM.

Main Menu
Write for LQ is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration