Re: Database Planning
"Sica, Bill" wrote: 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. For all the people who've never designed long-term databases, it should be stated: If you sell a book or buy a book at a given price, nothing says that price will be the same the next day. books: id, name , price sales: id, date, customer soldbooks: book_id, sales_id, price - this is the price, copied from "books.price" at the time of creation of the new entry because the book price might change in the future, and then your sales history is wrong. -- Michael T. Babcock (PGP: 0xBE6C1895) http://www.fibrespeed.net/~mbabcock/ - 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
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
RE: Database Planning
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
Re: Database Planning
I just got done figuring out how to --100%-- normalize a database to make it very flexible. Methinks that Oracle has done this in some fashion, as they claim to be able to make an intersection from any row in any table to any other. A note: Objects, Actors, and Attributes use the names of the tables having 0:1 relationships to them as the value in their name . Example: Actors ActorID=1,TypeID=1,Name=Company ActorID=2,TypeID=1,Name=Company ActorID=3,TypeID=2,Name=Member Objects ObjectID=1,TypeID=1,Name=Schedules ObjectID=1,TypeID=2,Name=Bookings ObjectID=1,TypeID=2,Name=Bookings the database application code/constraints, or scripts accessing the database have to enforce that no ObjectID,ActorID,AttributeID EVER gets used twice and only appears in once of the tables in 0:1 relationship to it. I welcome any comments. I've got the diagram in *.bmp (huge). I'll download a graphics program and convert it to *.jpg. It'll be at: http://64.177.230.204/public/VeryNormalised.jpg http://64.177.230.204/public/VeryNormalised.png - 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
