DB Normilazation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have just taken my first stab at trying to normilize a testdb that I have
created in SQL 2000. I want to get feedback and or tips and hints on whether
or not I've normalized this DB to the 3rd. Form. Below you'll find the
information on the Tables I've created. Thanks in advance:

tbl_Customers:
CustomerID
CustomerTypeID
CustomerName
DateCreated
CustomerNotes
DateLastModified

tbl_CustomerType (look up table):
CustomerTypeID
CustomerTypeName
Description

tbl_CustomerAddress:
CustomerAddressID
CustomerID
DateCreated
Address
City
State
ZipCode
DateLastModifed
AddressNotes
IsPrimaryAddress

tbl_Contacts:
ContactID
CustomerID
ContactTypeID
FirstName
MiddleName
LastName
PhoneNumber
EMail
IsPrimaryContact
ContactNotes
DateLastModified

tbl_ContactTypes (look up table):
ContactTypeID
ContactTypeName
Description

tbl_Orders
OrderID
CustomerID
OrderNotes

tbl_OrderDetails:
OrderDetailsID
OrderID
ProductID
DateOfPurchase
Quantity
DateOfShipment
OrderNOtes

tbl_Products
ProductID
ProductName
ProductPrice
ProductDescription
UnitsInStock
 
Honestly, with a quick glance, these tables look pretty normalized.

On a different note, I'll suggest some rules that I use that speed up
development for me.

1) All tables should be singular, no exceptions. you have half and
half. when your typing and you don't know the name of the table, it
will save you time in the long run if you're consistent.
2) By following rule #1, each table should have a primaryKey that is
the name of the table plus ID. So, Order (I would lose the tbl_) would
have primaryKey called OrderID
3) You should never prefix any column with the name of the table. This
should be implied because you're already in that table. For example, I
would change CustomerName to just Name (even though Name is a keyword
--> contrary to what other people may argue this will not cause any
problems).
4) The only exception to 3 is if it's a candidate for a foriegn key to
another table. So I would leave CustomerTypeID alone.

These are merely suggestions, so don't take them too seriously, I was
just bored and decided to read some posts ;)

-dave
 
Back
Top