March 3, 2005 at 9:26 pm
I have to 2 tables.
TABLE - Company
COLUMNS - CompanyID(PK), CompanyName
TABLE - Address
COLUMNS - AddressID(PK), AddressTypeID(FK), CompanyID(FK)
2nd Normal Form states that non key fields must depend on the Primary Key.
In my case above, how does the CompanyID in the Address Table depend on the AddressID?
Have I misunderstood 2nd Normal Form?
March 4, 2005 at 7:29 am
Actually, I believe the second normal form requires that there are no partial key dependencies. In other words, if you have a composite key, columns that rely only on only one column of the key should be broken out to a different table.
Anyway, I'm assuming your relationship is that by knowing the AddressID(PK), you can determine the company that resides there. Do you actually have address fields, city, etc?
Also, one problem is that with regard to office space you will have many companies sharing the same or similar address (depending on whether or not small differences such as suite are captured - and therefore have a different AddressID). If you don't capture something like suite, and many companies have the same addressID, then you have what I think is called MVP's(multi-valued dependencies). It might make sense to have the addressID be a FK in the Company table, rather than having the Company be a FK in the address table.
This is all my opinion though - I just recently started reading up on normalization...
March 4, 2005 at 7:33 am
Actually, regarding my last statement (MVP's), they wouldn't be able to occur here (without capturing a suite, that is) because your primary key (AddressID) would prevent them.
I definitely think an AddressID FK should be in the Company table and maybe the CompanyID FK should be reconsidered...
Again, just my two cents.
March 4, 2005 at 1:12 pm
hi,
i think you are getting it backwards.
you have created a primary key on the address table that in reality is a "Proxy" - (I cannot think of the correct term right now) for the real primary key which is the companyID and the address type
the structure you list looks like there is a one to many relationship between a company and its multiple addresses. To properly define any specific address you need to know the company and the address type (and then possible another column for specific info for the type)
for an example
JCPenny's has 5 locations in Milwaukee
the locations are of 3 different types: Warehouse, outlet, and store
and the stores are north and south
unless i am reading the tables you described incorrectly this could be what is going on
tal
March 6, 2005 at 4:23 pm
Thanks for you're input guys. In a nutshell, a Company may have 1 or many Addresses. For example, the first AddressType may be a Street Address, the second may be a Postal Address etc....
I'm really trying to establish whether I'm designing the Database correctly. Is what I'm doing the best way in terms of Normalization? At the moment, In my opinion, it is correct however, when you apply 2nd normal form to what I have done, it looks like I have broken 2nd normal form as the CompanyID is not dependant on the AddressID.
Have I got it all wrong? or is what I have done the correct way?
March 7, 2005 at 7:59 am
Hi,
You have it but do not know it.
as I tried to say (albeit poorly) is that the primary key in your address table is more than one column. The column you made (AddressId) is not the REAL primary key. it is used in stead of the real one that is made by using 2 columns fk_Company and fk_AddressType
the idea being that a particular company will have only one address of the specific address type.
that is entirely acceptable under the 2nd law. Dont let the fact that you have arbitraily pasted an Integer ID column in the front of the table cloud your mind as to what the "Real" primary Key is "Company X AddressType"
HTH
Tal McMahon
March 7, 2005 at 5:33 pm
March 7, 2005 at 6:03 pm
I think what Tal is trying to tell you is that you created AddressID as a surrogate key and that's fine. But the surrogate key is standing in for the logical primary key of of AddressType and CompanyId.
You could just as well have created the table without AddressId and used AddressType and CompanyId as the primary key (a two column composite PK).
March 7, 2005 at 8:03 pm
March 7, 2005 at 9:13 pm
March 8, 2005 at 7:12 am
hi,
As I mentioned earlier I think of the surrogate key as a proxy for the real key, hence my loss of the true name "surrogate".
More to your question "So, is it fair to say that Normalization rules do not apply to surrogate keys?"
this is one of the old debates in database modeling. Some would say that if you need a surrogate key that you havent done your job correctly in creating the primary key. Others say that the Time and programming that you save by using a surrogate key outweighs the hit you add by using one.
I tend to use Surrogate keys because a lot of my programming deals with using controls that do not play nicely with more than display and value fields.
of course as with everything YMMV
hope that helps,
I would also suggest Celko's book "SQL for Smarties." He is among the more rigid in his theory but the book is very well laid out and I have learned a lot from it.
HTH
Tal McMahon
March 8, 2005 at 7:40 am
I'd like to point out that Address Type and Company ID are not logical keys here either since this condition could repeat itself. The idea of using the surrogate key is correct in this situation as the problem was presented since this should be unique.
Here's a short example:
Address Type(s): Main, Alternate
Company ID(s) : 1 - JC Penny
Obviously JC Penny has 1 main address, but has alternate addresses everywhere. Of course, you could refine Address type; however, eventually you would run into a situation that requires the same address type.
For example, an address type might be vacation home.
Though most of us couldn't afford it, 1% of the population probably has multiple vacation homes.
Using the surrogate key is o.k. as the database/tables have been presented to this point.
March 9, 2005 at 9:52 am
good points EM
March 9, 2005 at 6:26 pm
I have done some investigation into this topic since I started this thread. Upon what I have researched in conjuction with the responses to this thread so far, we have it all WRONG!
I'm not saying that what I have done and what each person has said so far to this topic won't work, I'm saying according to Normalization rules I did not design these tables correctly.
Apparently, the way I have designed, I have broken 2nd Normal Form in the Address Table becuase the CompanyID is not dependant on the AddressID regardless of whether it's a surrogate key or not!
Apparently the correct way to comply with 2nd Normal Form, is to create another Table, call it what you like, I have called it CompanyAddress and place the CompanyID, AddressID and AddressTypeID into this table. By doing this we have removed the CompanyID from the Address Table and now the Company, Address and the new CompanyAddress Tables have now complied with 1st, 2nd and 3rd Normal Form.
Of course there is 1 probably 1 of many reasons that Normalizing can be a problem because now I require 5 JOINS to get Company, Contact, ContactType, Address and AddressType Information.
I guess 1 has to way up whether this will be a problem and if it is then you can start breaking Normalization rules.
I'm open to suggestion folks, but I think I have now fully understood 1st, 2nd and 3rd Normal Forms.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply