Re: MySql - creating a schema.

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-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


--
martin@ | Martin Gregorie
gregorie. |
org | Zappa fan & glider pilot


.

Relevant Pages

  • Re: Combo Box AfterUpdate Help
    ... its a shame that Morrison now finds it so ... >> "I have two tables in an ordering system database. ... >> in compliance with the rules of normalisation. ... >> possibility ) for being hesitant about your design. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Query to search multiple identical tables
    ... separate from the newsgroups and the advices given in these newsgroups are ... whether you referred to making your database, with you as the developer, as ... correct and then to design easy and intuitive GUI for your users. ... know how to apply the Database Normalisation technique). ...
    (microsoft.public.access.queries)
  • Re: string from column value
    ... Pro SQL Server 2000 Database Design ... What is wrong with the schema? ... >> Pro SQL Server 2000 Database Design ...
    (microsoft.public.sqlserver.programming)
  • Re: How to apply (rather than understand) OO?
    ... But the database desing is always first. ... Even if he has control of the schema, ... the focus of good schema design is data and the focus of good OO ... The original poster was asking how to start applying OO ...
    (comp.object)
  • Re: Normalization and AVG across columns
    ... columns (Proposal Phase Rating, Preconstruction Phase Rating, etc.) ... This is a bit of database theory that covers normalisation Forget that you are using Access for a while, because Access 12 might have new features. ... you need to answer these questions before you commit to a database design. ...
    (comp.databases.ms-access)