The actualy tool I find that works the best is a sheet of paper.. or a white
board. I write down all the fields that are required. Then find out what I
can break out (normalize). After that I try to figure out what I can slice
away and still get the required data. For instance, say the book price is
$19.44. You also have a base cost of $18, and 8% tax. You can always write a
query to find out the total cost of any book, and get rid of anything to do
with the total cost in your tables. Finally I find that primary keys that
have no relation to data works the best.. You could have used the ISDN
number for the primary key of the book table, but it works out a whole lot
easier the other way. Also what I see all the time is text fields as primary
keys.. I hate that. Finally come up with some sort of logical naming
convention. There is a system called the Leszynski Naming Convention (LNC),
which I use where I work.
cost:
cost_id
book_id
curCost_base
intCost_tax_rate
curCost_total
-----Original Message-----
From: René Tegel [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, April 18, 2001 2:45 AM
To: Jeff Holzfaster; General MySQL List
Subject: Re: Database Planning
well, start with reading a book about designing databases, especially about
the subject 'normalisation'.
normalisation is a way to split up data ('entities') in a way that every
piece of data only occurs once in your database. (although sometimes for
practical reasons you may decide otherwise, but normalising your data is
always a good first step!)
Since you say newbies will like this, i'll try to give an example.
suppose you have a database of books. The database stores the book, it's
title and so on, the author, the publisher and the bookstores that sell this
particulair book:
book title description author author_address publisher publisher_address
bookshop bookshop_address [bookshop bookshop_addr], []
well, you could try to make this fit in one table, but you'll get a tough
time.
so, you split up ('normalise'):
books:
book_id
ISDN
title
description
author_id
publisher_id
authors:
author_id
author_name
author_addr
author_email
publishers:
publisher_id
publisher_name
publisher_addr
publisher_phone
publisher_email
bookshops:
bookshop_id
bookshop_name
bookshop_addr
bookshop_phone
availability:
available_id
book_id
bookshop_id
So, you split up your data in more elementary parts.
Especially notice how you create a table that links books to the bookshop
that sell those books, you only need the two id's from the book and the
bookshop.
If your very smart, you might figure out by yourself further. Elsewise i
still recommend to go to a (online) bookshop and get a nice book about
developing databases.
gl,
rene
----- Original Message -----
From: "Jeff Holzfaster" <[EMAIL PROTECTED]>
To: "General MySQL List" <[EMAIL PROTECTED]>
Sent: Wednesday, April 18, 2001 4:01 AM
Subject: Database Planning
> I realize this may be a little OT but I'm sure many of us MySQL rookies
> would be interested in the responses.
>
> I would be interested in hearing how someone goes about planning the
layout
> of their database including how they determine what tables, fields, column
> types, queries, etc they will need. How do you start the process? How do
> you determine the correct questions to ask of your application? Do you
use
> some kind of diagramming tool? Blah... Blah... Blah...
>
> Thanks!
>
> Jeff
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
