General rules for building a database

[Venom Rush]

Hi everyone

I'm looking for advice on building up a database. Any sort of general do's and don't s. The one and only thing that I realize is that putting ALL my info into one table is not a good thing.

This is the first database I'll be creating so any help would be great.
Thanks in advance.

[trudge]

Hi everyone

I'm looking for advice on building up a database. Any sort of general do's and don't s. The one and only thing that I realize is that putting ALL my info into one table is not a good thing.

This is the first database I'll be creating so any help would be great.
Thanks in advance.

Like most design projects, things are usually done backwards. In other words, your needs and constraints will determine the design of your database.

Some reading that has (and still does) help me tremendously (in order of density):
Database Design for Mere Mortals by Michael J. Hernandez
MySQL by Paul DuBois
Data & Databases: Concepts in Practice by Joe Celko

Also, assuming of course you are going to be using MySQL are the reference and user documents available at mysql.com and lists.mysql.com. See also the newsgroup alt.comp.databases.mysql

Whenever I am tasked with building a database I usually go through these stages.

Information gathering. Talk to everyone who will input, extract, or somehow use the data. Find out how they use it now. How would they like to use it?

Start grouping similar kinds of data. Data is one of 3 things: an object, a transaction, or an attribute. For our purposes let's say your are building a database to hold information about your book collection. You would make a list of things you want to keep track of.
Primary author last name
Primary author first name
Secondary author last name
Secondary author first name
Corporate author
Title
Publication date
ISBN
# pages
Type of binding
Subject heading

So if this the information you are going to store, I would break it into 3 groups, which will become tables.
1 - information about the physical book
2 - information about the author/s
3 - information about the subject

In #1 I would put Title, ISBN, binding, Pub. date, # pages.
In #2 I would put Primary, secondary and corporate author information
In #3 I would put 1 subject (don't make the mistake of putting numerous subjects into 1 field)

When you define your tables, you will include a Primary Key field, which will be used to tie information in the other tables together about this object.

Up to this point I do this all with good old paper and pencil - it's much faster to change things and move / add / delete things.

When I think I have most of my data collected an organized, THEN I hit the keyboard and begin defining the tables, as determined by my data groupings.

Next I populate the fields with some data and do some queries. It's important to test your queries for completeness and correctness. The worst thing that can happen is either getting back wrong information (and not knowing it) or incomplete information (and not knowing it).

This is where normalization will help you. Whenever you read about RDBMS you will hear about normalization. Usually I try to get to 3NF.

Now the real testing begins, with real users. I usually try to create specific queries that will cover all the possible outcomes, and test to make sure they return what is expected.

This is where I leave you to start your own learning curve. Designing a database is not trivial if you are going to a proper job of it. Be prepared for some head-scratching and wall-banging, but remember that your problem has likely already been solved by someone else, so just ask. Good luck and have fun eh :)

[Venom Rush]

Thanks so much trudge. That info is of GREAT help. ;)

[jbennet]

yeah draw out your ER diagram and data dictionary first the design paperwork will make your life easier

dont use spaces in the field or table names!