[Gambas-user] MySQL question

[nando]

It is correct to have just one table with all the countries data together and
a column with the country in it.
The speed and multi-user access issues are non-existant for mySQL.
It is even more correct to have it normalized so that the country table uses
an arbitrary ID like 'countryid' as smallint (2 bytes) indexed.  It is this code
you use in the main data table and not the actual country word.
This is how (very, very) large relational databases are setup.
It is very fast and efficient.
I'll say having a table for each country is what
many colleagues and I would avoid as bad database design.
MySQL was designed to do it.
I have tables with 15 million records in it - 20,000 is nothing.

---------- Original Message -----------
From: Ron Onstenk <ronstk at ...239...>
To: mailing list for gambas users <gambas-user at lists.sourceforge.net>
Sent: Fri, 11 Aug 2006 05:33:01 +0200
Subject: Re: [Gambas-user] MySQL question

> 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
> 
> -------------------------------------------------------------------------
> Using Tomcat but need to do more? Need to support web services, security?
> Get stuff done quickly with pre-integrated technology to make your job easier
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
> _______________________________________________
> Gambas-user mailing list
> Gambas-user at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/gambas-user
------- End of Original Message -------