Can databases form automatic calculations?

#11

Cerebral Believer wrote:

Quote:

"Ed Prochak" <edprochak (AT) gmail (DOT) com> wrote in message
news:1158582698.706481.172300 (AT) m73g2000cwd (DOT) googlegroups.com...

[]

Another point of terminology, a TABLE is the implimentation of a
relation. When you combine data from two or more tables, it is a JOIN.

You really need to step away from your spreadsheet view and read about
the concepts of Relational Data Model. You might just start from the
beginning, read Codd's paper about this:
http://www.acm.org/classics/nov95/toc.html

Thanks for that info, you are right I do visualise databases as being very
similar to databases. I don't see why a specific column/row entry can not
perform a function and return the value to a the same location. It seems
like protracted work to achieve the same things I could using a spreadsheet
but in database format - still I am determined to get there, and I will,
eventually!

You just have to realize the basic difference. A spreadsheet is
designed to perform calculations. that's its raison d'etre. (sure hope
I spelled that right). A database is designed to store data efficiently
and provide convient, flexible access.

Quote:

[]
I found the answer, again quite simple, but requiring back-tick quotes
(which I had not put round the procedure name), which is not specified on
the MySQL website, neither in the reference material or the examples. So
this works:

CREATE PROCEDURE `008_sales`()
SELECT SUM(`Quantity Ordered`) FROM `Sales Detail Information` WHERE
`Catalogue Number` = "FBDCD008";

Anyone know how I can get a column to automatically run that procedure?
Is
that even possible?

Regards,
C.B.

If you get that procedure to run, where do you want the result to go?

To return the result to the same column/row, were it was called from. I
don't think this is possible though given what I have learned about
databases so far. Have you actually used myphpadmin? Anyway, I have been
experimenting with VIEW statements, and it seems like I am redesigning my
Totals table as a series of VIEW statements.

Much better.

Quote:

Actually, unlike a spreadsheet, you do not want to run this
automatically. You probably want to get the SUM() for your report
screen. There is no reason to store this value since it can be
computed easily when needed, with the advantage that is is correct when
needed. So create a function to call or create a VIEW which your
report application can use.

Yes, it is a different way of thinking, and perhaps more logical - that way
I can pull of a report of sales for a period, rather than just have an
ongoing running total.

Regards,
C.B.

Now you are starting to get it. I really do urge you to read more
about the concepts of relational databases. You are also going thru
the normal transition period that happens when learning a new
programming language or computing model. You initially view everything
in terms of what you knew beforehand, go thru a struggling phase where
things don't seem to work as expected, 'til finally you start thinking
in a new way that fits what you are learning.

Good luck. And keep coming back with questions.
Ed

#12

"Ed Prochak" <edprochak (AT) gmail (DOT) com> wrote

Quote:

Cerebral Believer wrote:
"Ed Prochak" <edprochak (AT) gmail (DOT) com> wrote in message
news:1158582698.706481.172300 (AT) m73g2000cwd (DOT) googlegroups.com...

[]

Another point of terminology, a TABLE is the implimentation of a
relation. When you combine data from two or more tables, it is a JOIN.

You really need to step away from your spreadsheet view and read about
the concepts of Relational Data Model. You might just start from the
beginning, read Codd's paper about this:
http://www.acm.org/classics/nov95/toc.html

Thanks for that info, you are right I do visualise databases as being
very
similar to databases. I don't see why a specific column/row entry can
not
perform a function and return the value to a the same location. It seems
like protracted work to achieve the same things I could using a
spreadsheet
but in database format - still I am determined to get there, and I will,
eventually!

You just have to realize the basic difference. A spreadsheet is
designed to perform calculations. that's its raison d'etre. (sure hope
I spelled that right). A database is designed to store data efficiently
and provide convient, flexible access.

Hello Ed,

Thanks fo ryour reply - I am just starting to get my head around the idea of
the differences between spreadsheets and databases.

Quote:

[]
I found the answer, again quite simple, but requiring back-tick quotes
(which I had not put round the procedure name), which is not specified
on
the MySQL website, neither in the reference material or the examples.
So
this works:

CREATE PROCEDURE `008_sales`()
SELECT SUM(`Quantity Ordered`) FROM `Sales Detail Information` WHERE
`Catalogue Number` = "FBDCD008";

Anyone know how I can get a column to automatically run that
procedure?
Is
that even possible?

Regards,
C.B.

If you get that procedure to run, where do you want the result to go?

To return the result to the same column/row, were it was called from. I
don't think this is possible though given what I have learned about
databases so far. Have you actually used myphpadmin? Anyway, I have
been
experimenting with VIEW statements, and it seems like I am redesigning my
Totals table as a series of VIEW statements.

Much better.

I managed to achieve my goal using the following code:

create view total_sales
select `P`.`Artist` AS `Artist`,`P`.`Title` AS `Title`,`P`.`Format` AS
`Format`,`P`.`Type` AS `Type`,`P`.`Catalogue Number` AS `Catalogue
Number`,`P`.`Price` AS `Price`, sum(`SD`.`Quantity Ordered`) AS `Total Sold`
from (`remotedb`.`Product Information` `P` left join `remotedb`.`Sales
Detail Information` `SD` on((`P`.`Catalogue Number` = `SD`.`Cat Num`)))
group by `P`.`Catalogue Number`;

Quote:

Actually, unlike a spreadsheet, you do not want to run this
automatically. You probably want to get the SUM() for your report
screen. There is no reason to store this value since it can be
computed easily when needed, with the advantage that is is correct when
needed. So create a function to call or create a VIEW which your
report application can use.

Yes, it is a different way of thinking, and perhaps more logical - that
way
I can pull of a report of sales for a period, rather than just have an
ongoing running total.

Regards,
C.B.

Now you are starting to get it. I really do urge you to read more
about the concepts of relational databases. You are also going thru
the normal transition period that happens when learning a new
programming language or computing model. You initially view everything
in terms of what you knew beforehand, go thru a struggling phase where
things don't seem to work as expected, 'til finally you start thinking
in a new way that fits what you are learning.

Thanks for your advice. As I have time I am reading through the various
linlks and websites that have been provided, and I have a few books now on
MySQL and PHP. I have a tight dealine for my initial project, so I think
after setting up a basic database, I can then spend time refining it as I
build on my knowledge.

Regards,
C.B.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Can databases form automatic calculations?

comp.databases comp.databases

Re: Can databases form automatic calculations? - 09-19-2006 , 06:56 AM

Re: Can databases form automatic calculations? - 09-19-2006 , 10:53 PM