On Tue, 8 Jul 2003 23:21:13 +0000 (UTC), in mailing.database.mysql
dt*******@provoix.com ("David Thompson") wrote:
| Looking for a book to help me develop a philosophy for building
| databases (particularly on MySQL). And then taking them from concept to
| construction.
|
| Something like.... Start by asking which queries you will be performing,
| then define all data needed for each of those queries, then progress to
| normalize this data, etc.
|
| Any Ideas....thanks...
Step 1.
Write out a Purpose Statement for your database. The more detailed the
better.
Step 2.
On another piece of paper write out 3 headings People, Events, Things.
Scan through your Purpose Statement for keywords for each section i.e.
People: client, employees, customers
Things: Stock, Books, Inventory
Events: Sales, Hiring
Step 3.
Under these keywords, roughly, fill in some field names i.e
People: Client: ClientName, Address
Things: Books: Title, Author, Price, location, Genre
Events: Sales: Date, InvoiceNumber, Salesperson, customer info
The idea here is just to get the information while you are
concentrating on each table.
When filling in this information ask yourself - what information does
this table need to have.
Except for the Events table(s) there should be no reference /
information outside of that table i.e. a customer table will only have
the customers details, no sales information, no stock information etc.
Step 4.
Revise the fieldnames that you will be using. Break them down to the
smallest logical component i.e.
People: Client: Address breaks down to Street, Suburb, State, Zip etc
Things: Books: location could be Shelf location, warehouse bin
location etc.
Events: Sales: check that you are capturing all the information that
you require for your reports.
Refer back to your Purpose Statement for additional information that
you might have missed. Repeat step 4.
Step 5.
Revise the tables. Look for any item that might have repeating data
that could be moved to its own table. An example would be the Genre
field in the Books table. Create the table and repeat from step 3.
Step 6.
Determine the primary key for each table. If there is not an obvious
one then add a new field to the table. (see note below)
Step 7.
Grab another sheet of paper and this time draw a grid (4cols x 4Rows)
for each table. The layout should be similar to that of the table
design.
This is going to serve 2 purposes. 1) it will give you the
relationships between the tables and 2) you will see if there are any
orphaned tables.
On the top row of each grid fill in some important data fields.
Fill in some pretend data for your People and Things tables only.
Now for the Event table(s) - play computer. By this I mean pretend you
are the computer capturing the transactions. Pull information from
your other tables that match the fields on your Event table.
Don't forget to do this to other tables in the People and Things area.
Try adding a new book to the list. Does it have a reference to the
Genre table?
Once you have done that - look at how the tables 'relate' to each
other. Draw the necessary lines.
Revise your table design as necessary.
Step 8.
On another piece of paper (or spreadsheet). Write out your table names
and their fieldnames. Fill in the datatype and any formatting
consideration (phone numbers, SSN etc). Also decide on default values
for the fields.
Make sure that your primary/foreign key datatypes match.
Step 9.
Go create your database within the db application.
------------------------------------------
Defining a Primary Key.
I'll use the Customer table as an example.
Table: FirstName, LastName, DOB, Street, Suburb, State, Phone.
Ask yourself, what field; in this table; is unique to that record?
In the Customer table there are no fields that are unique because:
Several people have the same first name or last name
Customers could have the same DOB (unlikely but possible)
The same goes for the address details.
Option - create a new field.
In the Events table you might have:
Sales: InvoiceNumber, Date, EmployeeNo, Book
It is pretty obvious the unique field here.
Fieldname Naming Convention
---------------------------
These are some of the conventions I use.
You may want to prefix each non-event table Fieldname with the
tablename i.e. Cust_FirstName, Sup_SupplierName etc.
Never use spaces in fieldnames - use underscores (saves headaches with
queries and migration to other databases).
Use mixed casing for the fieldnames (although mySQL doesn't honor
this).
Indicate primary/foreign keys with the postfix of ID, PK or FK
i.e. Cust_Customer_ID (Customer table)
Customer_FK (Sales table)
Sample Purpose Statement
------------------------
I want to track the number of albums (CDs, LP and singles) that I own.
I want to know when and where I purchased the album and how much it
cost.
I would like to be able to search the database by Artists Name and the
music type.
I need to have a list of tracks on each album and each songs playing
time.
A nice little touch would be, I could give the album and each song a
rating value. This is so that I could create my own music cds with all
my favourites songs of a particular genre.
I store my music according to a numbering system so I need to be able
to find the album by this number also.
Another nice touch would be, friends borrow by albums so I would like
to know who has a particular album, when they borrowed it. On a
report/query only those albums that have been leant out will be
listed.
------------------------
The above gives you enough info to create the database. First create
the database with OUT the 'nice touch' items. Once designed and
completed add in the 'nice touch' features. With a good, solid design
this will not cause too much additional work.
-----------------------------------------
HTH
---------------------------------------------------------------
jn****@yourpantsbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------
