[Gambas-user] MySQL question

[Ron Onstenk]

On Thursday 10 August 2006 20:16, Marco Gusy wrote:
> Alle 15:17, giovedì 10 agosto 2006, James Hatridge ha scritto:
> > Hi all..
> >
> > I have a database named "stamps" (I'm a stamp dealer), it has X number of
> > tables (unknown before opening "stamps") with names like "US", "Germany",
> > "Russia", and so on. How can I make a list so that the user can pick which
> > table to use?
> >
> > Thanks for your help!
> >
> > JIM
> 
> It's not the best Idea to create a table for each stamp nationality.
> You should create a table wehre the first field contains stamps nationality, 
> and the rest of fileds like the tables you want to index.


I do not fully agree with that.
If the total collection have 20000 stamps and 'Belgium' only 2
then every update to a record of 'Belgium' affect the whole table/file.
For quick access separate tables are the better way.
For some other things as backup/restore after crash spread of data
across more tables are or may be more convenient.

If the different countries are equal spread in quantity then a
single table can be good for easy selecting. 

I did some work for nordphila.de and there both ways are used.
1 table for the stamps parameters and a multi table system for
detailed descriptions.
the one table versus multi table are handled by different progrmas/users.
After the description editor made a error, the core bussines stay working
and the descriptions could/are comming from backup.
Safe and nice.

It's not the best Idea does not always count.
It depends on which data is handled and in which way.

> 
> Then you can open a table with "Select * from stamps where nationality='US'; " 
> and see the table as before.
> To see a list of nationalities "Select distinct nationality from stamps;"

And when the 'nationality' is in a separate table with a flag as 'EnableList' 
you can easy limit the access for a country and access (distroy) the other
country tables.
 
Both ways have a pro and against.
It is the programmer that can made the best solution for his
choice, depending on his capabilities and data content.

We can only give hints what is possible.
For the 'best way' the reason should given why or in which condition.

Anyway I could many times improve speed by using multi table systems.
Simple because the OS (windows/unix) have more speed then any SQL server
in filtering records than in the select query can be done.


Ron