Database Design and Normalization

I am developing an "AddressBook" like project in VB.NET. While developing database in SQL Server2005, i want to follow the principals of normalization and keep its degree of normalization as high as possible.

Fields i am using are these.

Create table AddressBook_Contact
(
Contact_ID int Primary Key,FirstName varchar(15),MiddleName varchar(15),LastName varchar(15),BirthDay datetime,HomeCountry varchar(20),HomeState varchar(20),HomeCity varchar(20),HomeMobile varchar(40),HomePhone varchar(40),HomeMobile varchar(40),HomeFax varchar(40),HomeZipCode varchar(10),HomeEmail varchar(50),HomeAddress text, 
BusIndustry varchar(20),BusCompany varchar(20),BusWebsite varchar (30),BusCountry varchar(20),BusState varchar(20),BusCity varchar(20),BusMobile varchar(40),BusPhone varchar(40),BusFax varchar(40),BusZipCode varchar(10),BusEmail varchar(50),
BusAddress text,Note text
)

if i create all fields in one table..is it against normalization? and what will be the impact on table except it become slow due to large increase in size by the time.

If i create separate tables for Contact(Biodata,HomeInfo,BusinessInfo)

create table AddressBook_Bio
(
BookID int Primary Key,
Title varchar(15),
FirstName varchar(15),
SecondName varchar(15),
LastName varchar开发者_C百科(15),
BirthDay datetime,
Note text
)

create table AddressBook_Home
(
'My question in this case is should i declare BookID as foreign key or primary key to the table and why?'
FOREIGN KEY (BookID) REFERENCES AddressBook_ContactBio(BookID),
HCountry varchar(20),
HState varchar(20),
HCity varchar(20),
HMobile varchar(40),
HPhone varchar(40),
HFax varchar(40),
HZipCode varchar(10),
HEmail varchar(50),
HAddress text
)

create table AddressBook_Business
(
same issue as "AddressBook_Home"
)

Thank for caring to expressing and share your opinion, knowledge.

---

Yes, I believe that the large table you wish to create is not normalized. The impact it may have is that you can have NULL values in your table, which wastes space.

I think you would want to declare BookID as a primary key, since it appears to be the only key of that table, and each table gives different information of a person. This would be similar to partial-overlapping subclasses.

---

If there is only going to be one home address and one business address, then the first structure is not really "against normalization" - it is expressing the structure of the data.

If you want to allow multiple addresses (1, 2 or more, or even zero) you should create a separate Address table (possibly with a "type" column saying whether it is a home, business or other address).

The idea you give of 2 almost-identical address tables seems a bad way to do it since one table will suffice and you don't need to copy and paste to make two identical tables.

If you create an Address table, it should have BookID as a foreign key, and it should also have its own independent primary key.

The fact that you are wondering whether BookID should be a primary key on the *_Address table demonstrates that you are splitting something up into 2 tables when actually it should be one.

---

The highest normal form that is always achievable is 6NF. Put simply, a table that is in 6NF will have a key (which could comprise multiple columns) plus at most one additional attribute (note that attribute may have subatomic elements each with its own column e.g. address has subatomic elements zipcode, state, country, etc). So at the very least you should have distinct tables for Note, HMobile, HPHone, HFax, HEmail and Note, possibly BirthDate if it not part of a key (I would presume it is).