MySQL Server Forum - Dynamic web list question

Author

Dynamic web list question

Matt Silberstein

2005-08-09, 11:23 am

This is not a MySQL question per se. That is, I am not asking a
language question, but a design question. But I will be writing the
code in Perl/MySQL and it is likely a common issue.

Here is the situation. My site will have pages that will have a
dynamic number items, each with there own associated fields. Let's say
it is a list of books with pub date, etc. for each book. The customer
can add books to their personal list, remove books, change the field
contents, etc. The information will then be stored in a MySQL DB.

Here is my question. Suppose we have this list and the customer makes
some changes and says save. The "simplest" solution is to delete all
of the old entries for that particular book list and write the new
ones from the page. The drawback I see is that I lose, unless I work
to keep it, original date. Plus I am doing some "unnecessary" DB
writes. Most importantly since I am not looking I have no idea which
items were changed and which were not. My gut tells me that I will
care which items where changed and when, but I don't have a
specification for that at the moment.

Supposing that DB size is not an issue at all and that, after
reliability, I care most about speed (customer apparent speed), what
is the best approach to this?

TIA.

--
Matt Silberstein

Well ya see, Norm, it's like this. A herd of buffalo can only move as fast as the slowest buffalo. And when the herd is hunted, it is the slowest and weakest ones at the back that are killed first. This natural selection is good for the herd as a whole, b
ecause the general speed and health of the whole group keeps improving by the regular killing of the weakest members. In much the same way, the human brain can only operate as fast as the slowest brain cells. Excessive intake of alcohol, as we know, kills
brain cells. But naturally, it attacks the slowest and weakest brain cells first. In this way, regular consumption of beer eliminates the weaker brain cells, making the brain a faster and more efficient machine. That's why you always feel smarter after a
few beers.

Cliff on Cheers

Bill Karwin

2005-08-09, 1:23 pm

Matt Silberstein wrote:
> Here is my question. Suppose we have this list and the customer makes
> some changes and says save. The "simplest" solution is to delete all
> of the old entries for that particular book list and write the new
> ones from the page. The drawback I see is that I lose, unless I work
> to keep it, original date.

You should look at the INSERT ... ON DUPLICATE KEY UPDATE syntax in
MySQL. This might allow you to do what you want. For instance, in the
INSERT portion of the statement, set the original_date to NOW(), and in
the UPDATE portion of the statement, do not set the original_date, which
will let it retain the value it had previously.

See http://dev.mysql.com/doc/mysql/en/insert.html

> Most importantly since I am not looking I have no idea which
> items were changed and which were not. My gut tells me that I will
> care which items where changed and when, but I don't have a
> specification for that at the moment.

It's good to have gut feelings about the emerging requirements, but it's
hard to predict what will actually become needed. Extreme Programming
recommends to implement only what has been stated explicitly as a
requirement, and if new requirements appear in the future, deal with
them them. It might require some rearchitecting of your code and your
database schema, but inevitably some other new requirement that you
didn't anticipate will do that anyway.

Regards,
Bill K.

Matt Silberstein

2005-08-09, 8:23 pm

On Tue, 09 Aug 2005 10:01:13 -0700, in mailing.database.mysql , Bill
Karwin <bill@karwin.com> in <ddanfh02ckc@enews2.newsguy.com> wrote:

>Matt Silberstein wrote:
>
>You should look at the INSERT ... ON DUPLICATE KEY UPDATE syntax in
>MySQL. This might allow you to do what you want. For instance, in the
>INSERT portion of the statement, set the original_date to NOW(), and in
>the UPDATE portion of the statement, do not set the original_date, which
>will let it retain the value it had previously.

That should work. I can program SQL, but I don't think it the way I do
other languages. So I don't tend to know what it is going to give me
for free. Thanks.

>See http://dev.mysql.com/doc/mysql/en/insert.html
>
>
>It's good to have gut feelings about the emerging requirements, but it's
>hard to predict what will actually become needed. Extreme Programming
>recommends to implement only what has been stated explicitly as a
>requirement, and if new requirements appear in the future, deal with
>them them. It might require some rearchitecting of your code and your
>database schema, but inevitably some other new requirement that you
>didn't anticipate will do that anyway.

I understand the logic of that, I am not sure it meets my experience.
That is, of course there will always be changes and likely some
re-architecting. But having the designer/programmer providing some
forethought makes sense to me. I don't expect the users to have a clue
on what the program will need, but I would hope I would have some
notion of where the code is likely to go. That said, I don't have a
successful book on how to program and I did ask a question.
--
Matt Silberstein

Well ya see, Norm, it's like this. A herd of buffalo can only move as fast as the slowest buffalo. And when the herd is hunted, it is the slowest and weakest ones at the back that are killed first. This natural selection is good for the herd as a whole, b
ecause the general speed and health of the whole group keeps improving by the regular killing of the weakest members. In much the same way, the human brain can only operate as fast as the slowest brain cells. Excessive intake of alcohol, as we know, kills
brain cells. But naturally, it attacks the slowest and weakest brain cells first. In this way, regular consumption of beer eliminates the weaker brain cells, making the brain a faster and more efficient machine. That's why you always feel smarter after a
few beers.

Cliff on Cheers

Matt Silberstein

2005-08-12, 3:23 am

On Tue, 09 Aug 2005 21:59:07 GMT, in mailing.database.mysql , Matt
Silberstein < RemoveThisPrefixmatt
s2nospam@ix.netcom.com> in
< 4k9if1hnkjbs87q9qdfh
179m70r4794g6q@4ax.com> wrote:

>On Tue, 09 Aug 2005 10:01:13 -0700, in mailing.database.mysql , Bill
>Karwin <bill@karwin.com> in <ddanfh02ckc@enews2.newsguy.com> wrote:
>

[snip]
[color=darkred]
>
>I understand the logic of that, I am not sure it meets my experience.
>That is, of course there will always be changes and likely some
>re-architecting. But having the designer/programmer providing some
>forethought makes sense to me. I don't expect the users to have a clue
>on what the program will need, but I would hope I would have some
>notion of where the code is likely to go. That said, I don't have a
>successful book on how to program and I did ask a question.

I have been thinking about this and I was wrong. Get the job done!
I/we can get too easily lost in attempts to architect something for
the future. I was looking at the project I am working on now and
realized that I was building about 2-3 steps too far ahead. I need
something that works to show people. Then I can build something that
does it right. Then I can build the real one when we figure out what
it really is supposed to do.

Thanks.

--
Matt Silberstein

Well ya see, Norm, it's like this. A herd of buffalo can only move as fast as the slowest buffalo. And when the herd is hunted, it is the slowest and weakest ones at the back that are killed first. This natural selection is good for the herd as a whole, b
ecause the general speed and health of the whole group keeps improving by the regular killing of the weakest members. In much the same way, the human brain can only operate as fast as the slowest brain cells. Excessive intake of alcohol, as we know, kills
brain cells. But naturally, it attacks the slowest and weakest brain cells first. In this way, regular consumption of beer eliminates the weaker brain cells, making the brain a faster and more efficient machine. That's why you always feel smarter after a
few beers.

Cliff on Cheers

Sponsored Links