|
|
Posted by Ivan Marsh on 07/12/07 23:24
On Thu, 12 Jul 2007 21:36:35 +0000, Fenix [Youth Director] wrote:
>> Cool. The best advice I can give anyone about database creation is to
>> get a decent grasp of database normalization and indexing.
>
> Can you explain that a bit?
Well... I'm no expert on database normalization but the guidelines I try
to follow are that tables should be linked by irrelevant fields (for lack
of a better description) such as a list of customers or users should have
a primary key field that's a unique, auto-incrementing field and any
tables linked to that table simply include a corresponding reference to
that field, such as:
(a customer table)
CUST_NUMBER - auto-inc
CUST_LAST_NAME - char
CUST_FIRST_NAME - char
etc...
(an order table)
ORDER_NUMBER - auto-inc
CUST_NUMBER - int
PROD_NUMBER - int
etc...
(a product table)
PROD_NUMBER - auto-inc
etc...
When you create a new order for a customer that order gets an automatic
unique identifier and you populate the CUST_NUMBER field in the order
table with the unique identifier for the customer from the customer table
and the PROD_NUMBER field from the product table.
So the order table doesn't have to know anything about the customer
or product except the unique numbers automatically assigned to the records
at the time of their creation. That way you don't end up with issues of
having three different John Smiths in the customer table.
This also makes searches faster and the queries easier to write because
you only have to think about the fields that should match up at the time
you created the database.
Then you create indexes based on what you know or assume would be relevant
search criteria. You would want an index on the customer table for last
name and first name separately and most likely an index for last name and
first name combined.
I'm of the opinion that a lot of tables with fewer columns are better and
faster than a few tables with a lot of columns (this is of course
debatable) and your data should be broken apart as much as possible in the
most logical way as possible. Your customer data doesn't belong in your
order data just as product detail data doesn't belong in your order data.
You end up with queries like (this is pseudo code): SELECT * FROM ORDERS,
CUST, PRODUCTS WHERE CUST.CUST_NUMBER = '1234' AND ORDERS.CUST_NUMBER =
CUST.CUST_NUMBER AND ORDERS.PROD_NUMBER = PRODUCTS.PROD_NUMBER;
....and you end up with all orders for customer 1234 and the detail model
information for the product ordered.
It takes a bit of playing around planning and thinking about what parts
of your data should be broken out and how the tables should relate to
each other but at least you can be thankful that you aren't dealing with
the old database technology I started out with where once you created a
table it's structure couldn't be altered.
Sounds like you've chosen a fun and complicated project for someone with
not much experience with databases... you'll certainly have some
experience when your done.
Navigation:
[Reply to this message]
|