[Gambas-user] [slightly OT] No Problem with Gambas or MySql!

[Randall Morgan]

How old is this database? Is it MySQL or MSSQL?

I ask because there was a time when databases did not support transactions.
So some db designers did indeed use temporary tables for what was called
scratch tables. These were tables where updates were collected over a
period of time and then batch updates were ran (typically at the end of a
quarter hour). At that time, the scratch table was cleaned up. This was a
very poor practice and in a busy system resulted in many errors. Some
systems only updated the records after closing as a nightly batch process.
If you system is doing something this archaic you can look forward to a lot
of headaches. There are better ways to deal with these issues now,
transactions for one.

Today there is really no need for such measures. A well designed database,
unless working on millions of records at a time, should have need for such
temporary storage unless it needs to compose a record from multiple sources
which arrive at different times. I have had to deal with that in some
telemetry systems. But beyond that, I can't imagine a real need for such a
setup...

However, given that is the case. It sounds like you do have a way to locate
related records. So if you should have your solution.




On Mon, Oct 7, 2013 at 2:49 PM, Willy Raets <willy at ...2734...>wrote:

> On Mon, 2013-10-07 at 14:04 +0200, Caveat wrote:
> > Thanks Randall, we got some great discussions going here.  I think the
> > closing paragraph sums it up quite well...
> >
> > "
> >
> > So err on the side of caution and normalize your data until you have
> > the experience needed to know when to break the rules. If you always
> > normalize your data you will be correct 99.999991 percent of the time.
> That
> > last 0.000009 percent of the time you may never get to....
> >
> > "
> >
> > Of course, as I later said, my pennies example was a prime candidate for
> > normalisation!  And, I think, the case Willy is dealing with would have
> > been normalised (in an ideal world) from:
> >
> > | ID  | Gewicht |
> >    345         100
> >    345         100
> >    345         50
> >    347         200
> >    347         200
> >    347         200
> >    347         200
> >
> > to:
> >
> > | ID  | Gewicht |  Count  |
> >    345        100             2
> >    345        50              1
> >    347        200             4
> >
> > with a Primary Key composed of ID and gewicht (weight).
> > But, Willy is working with a legacy system, and we don't know for sure
> > why someone decided to make (for example) 4 identical 347-200 rows,
> > instead of one 347-200 row with a count of 4.  Perhaps it's a very busy
> > system and the designer thought having many people able to create and
> > delete arbitrary numbers of ID-weight rows would make it easier to
> > manage the counts/weights in a system with many concurrent users, rather
> > than having people queue up to modify the count on a normalised 347-200
> > row?  Who knows!
>
> The database is set up normalized with relations to other tables,
> preventing double data, except for this one exception.
>
> The table mentioned is the only one that is set up this manner and
> without a primary key.
>
> It is a coming and going of records and is never longer than about 150
> records. It is one of the smaller tables in the database.
>
> When decided what do make of one or more of these items, the manager
> wants to see all individual items part of an ID in a table (so record by
> record and not like 357 200 2). Next they select one (by entering a
> selected weight and clicking a button) and transfer it to production
> with added info on what to make of it, consumer price and more (like an
> added unique ID to serve as primary key and field refering to ID the
> record originated from).
>
> So each item eventually ends up in a new table with added fields and a
> primary key.
>
> So the table is just some kind of temporary place holder and there are
> some user requirement in how they want it presented.
> Now we could argue that it can be stored like 357 200 2 and next shown
> to user as 357 200 / 357 200, but that never happened historically.
>
> Since I need the VB-clients to work with the same database as well
> during migration period and don't want to spend time on them as I have
> my hands full building the Gambas clients, it will have to do.
>
> Once the VB-clients are out of the picture, I might implement it
> differently.
>
> Hope that clears it up a bit, maybe it is one of the 0.000009 percent of
> cases where an exception is made, even thought it can be implemented
> normalised?
>
> Slightly OT...maybe, but how OT are databases when building Gambas
> clients for them?
>
> But a lovely discussion...for sure ;)
>
> --
> Kind regards,
>
> Willy (aka gbWilly)
>
> http://gambasshowcase.org/
> http://howtogambas.org
> http://gambos.org
>
>
>
>
>
>
> ------------------------------------------------------------------------------
> October Webinars: Code for Performance
> Free Intel webinars can help you accelerate application performance.
> Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most
> from
> the latest Intel processors and coprocessors. See abstracts and register >
> http://pubads.g.doubleclick.net/gampad/clk?id=60134071&iu=/4140/ostg.clktrk
> _______________________________________________
> Gambas-user mailing list
> Gambas-user at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/gambas-user
>



-- 
If you ask me if it can be done. The answer is YES, it can always be done.
The correct questions however are... What will it cost, and how long will
it take?