On Wed, 26 Mar 2008 16:20:51 +0200, Mr. X. wrote:
> 2. How can I create my own schema ?
>
I'd suggest you get a good book on database design and get to grips with
the principles of Entity-Relation****p 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 cl***** for a
new Java project.
- decide how the entities are related and use this to draw an entity
relation****p 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 relation****ps 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 sup****t 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
--
martin@[EMAIL PROTECTED]
| Martin Gregorie
gregorie. |
org | Zappa fan & glider pilot
|
