On Wed, 26 Mar 2008 16:20:51 +0200, "Mr. X."
<no_spam_please@nospam_please.com> wrote, quoted or indirectly quoted
someone who said :

You just create files like this and load them with the online utility:

rem create database and define tables
mysql -uroot -psesame < create.sql
mysql -uroot -psesame < people.sql
mysql -uroot -psesame < roles.sql
mysql -uroot -psesame < projects.sql
mysql -uroot -psesame < projectlocales.sql
mysql -uroot -psesame < resourcebundles.sql
mysql -uroot -psesame < bundles.sql
mysql -uroot -pextras < context.sql
...

------------------

/** recreate the database */
DROP DATABASE IF EXISTS internationaliser;
CREATE DATABASE IF NOT EXISTS internationaliser;

--------------------

USE internationaliser;

/**
* translators, proofreaders, programmers and administrators.
*/
DROP TABLE IF EXISTS people;

CREATE TABLE people (
  /**
   * people are identified by unique initials.  If there are
duplicates,
   * break the tie with a number or extra letter.
   * The field is case-insensitive.
   * The initials (acual a user id) must pass muster by a system wide
   * configurable regex to determine what is considered acceptable.
   * To allow only letters, underscore and dot use [a-zA-Z\\\_\\.]++
   * in the initialsValidRegex field in the configuration file.
   */
  initials CHAR( 4 ) NOT NULL,

  /**
   * First name then surname
   */
  fullName VARCHAR( 30 ) NOT NULL,

  /**
   * email address
   */
  email VARCHAR( 50 ),

   /**
   * telephone, complete with international dialing prefixes.
   */
  telephone VARCHAR( 50 ),

  /**
   * a person could be any combination of translator, proofreader,
programmer, administrator
   */
  capabilities SET( 'translator', 'proofreader',
'programmer','administrator' ),

  /**
   * password or passphrase, case sensitive. Administrator or the
person themselves may modify it.
   * SHA-1 digest 40 hex digits. Because this is a digest, it is
impossible to work backwards
   * from this to the original password.
   * Most likely authentication will be handled via RFC2617 HTTP
digest, which is more secure
   * than basic plain text passwords, but not as secure as SSL-https.
The messages back and forth
   * are not encrypted.
   */
  encryptedPassword CHAR( 40 ),

  /**
   *  preferred language for receiving email and running the editor,
2-char abbreviation, lower case.
   */
  language CHAR ( 2 ) NOT NULL,

  /**
   *  preferred country for receiving email and running the editor,
2-char abbreviation, all caps, possibly null.
   */
  country CHAR ( 2 ),

  /**
   *  preferred locale variant for receiving email and running the
editor, 3-char abbreviation, all caps, possibly null.
   */
  variant CHAR ( 2 ),

  /**
   * Preferred way this user wants to have his emails encoded.  The
encoding must be supported both by Java,
   * see http://mindprod.com/jgloss/encodings.html on the server, and
by the recipient's email program.
   * It is crucial that this be a valid supported name.
   * Default is UTF-8.  In desperation, you could use US-ASCII which
supports no accented letters.
   */
  emailEncodingCharset VARCHAR (20),

  /**
   * People are optionally associated with an icon, but those icons
are not stored in the database.
   * They live in resource files, under names like people/mary.png or
people/tallblond.png  or DRF.png.
   * You might wonder why we don't just insist that the correponding
icon be named to match the initials. This way
   * you can reduce the number of icons in the resource file if
several people share the same icon.
   * WHen the icon is displayed, tooltip hoverhelp will tell you the
initials and
   * full name of the person it represents.
   */
   iconName VARCHAR( 30 ),

  /**
   * the preferred look and feel name, the long name, the  class
name.
   * Metal : javax.swing.plaf.metal.MetalLookAndFeel
   * CDE/Motif : com.sun.java.swing.plaf.motif.MotifLookAndFeel
   * Windows : com.sun.java.swing.plaf.windows.WindowsLookAndFeel
   * MacOS : it.unitn.ing.swing.plaf.macos.MacOSLookAndFeel
   * Watch out! when an administrator configures this, he will be
selecting
   * from a list of short names of L&Fs supported on HIS machine.
   * He must be careful that any L&F he picks is actually
   * supported by the machines this person will use.
   * If that L&F is not supported, it will revert to the locale
default.
   */
   lookAndFeel VARCHAR( 100 ),

  /**
   * We look up people by their initials.
   * We also use initials to link in other tables
   * to link to this information.
   */
  PRIMARY KEY( initials )
  );

....

I'd suggest you get a good book on database design and get to grips with
the principles of Entity-Relationship modelling and normalisation. The
schemas of virtually all good databases are designed this way:

- use your knowledge of the material the database is going to model to
 select the entities - rather like you decide on the major classes for a
 new Java project.

- decide how the entities are related and use this to draw an entity
 relationship diagram.

- meanwhile collect attributes for each entity and decide which
 are identifiers. Determine the unique identifier.

- normalise the entities to 3rd normal form and, preferably, get rid of
 derived values. This step is critical to designing a good schema.

- after normalisation the attributes will become columns in one or
 more tables which together represent the entity. The identifiers become
 the prime keys of the table(s).

- now project the prime keys down relationships in the 1:many
 direction and add them to the 'many' table as foreign keys.

- code up the tables in sql to define the database. Add indexes
 to support the prime keys and your first cut schema is done.

- during development you should run queries manually before building
 them into programs and use the database's query analyser to
 determine if any extra indexes are needed. Be sparing. Indexes can
 be expensive to maintain and very greedy for disk space.

I prefer to hold the schema as a single file with DROP statements
appearing before the CREATE statements because you can run it as
a single script script and know you haven't forgotten anything.
Ordering is critical: toy MUST define master tables before details or the
schema won't build and you MUST use the reverse sequence for DROP
statements. Keep editing and running the schema against an empty database
until it runs without errors.

Books? I personally like those by Chris Date. They are old now, but the
principles haven't changed. They are easy to read, have lots of examples,
and explain stuff pretty clearly.

HTH

Can I suggest that you get hold of a copy of Paul DuBois' excellent book
about MySQL:

http://www.amazon.com/MySQL-Developers-Library-Paul-DuBois/dp/0672326736/

It's a perfect introduction to MySQL for the absolute beginner, as well
as a very good reference for the more experienced user.

It will answer most of your questions and help you to get up and running
with MySQL.

And it's widely acknowledged as one of the best-written technical books
on the market.

David Harper
Cambridge, England