Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server ASP
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Server ASP Discussions about ASP programming with Microsoft's SQL Server. For more ASP forums, see the ASP forum category.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server ASP section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 3rd, 2006, 10:41 AM
Authorized User
 
Join Date: Apr 2006
Location: Stockholm, , Sweden.
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default relationships - why really?

Hi all,

I've done my database studies and know about normalizing, relationships, primary keys and so forth. However, when it comes to actual developing of web sites, I've always skipped setting up relationships, and at times, the primary and (especially) foreign keys.

Now I'm designing my first db in Asp.Net 2, and the thought struck me - Why should I do this?

I guess the reason for setting up relationships is that the website should not get any trouble if rules are violated or poorly written, that is, data is input in an Orders table but not the Oredered_products table or the like. The only reason I can think of for using primary keys (when not defining relationships anyway) is to get the Insert, Delete, and Update statements of GridViews etc to work.

Or am I missing something? For many years, I've been told that the keys plus relationships taken together speed up the SQL queries, but, honestly, I can't see why they should. And I guess millions of web developers do like I do - skip the relationship thing altogether.

Please enlighten me, someone! (Or let's have an interesting discussion on this topic - if there is something to discuss, that is!)

Pettrer

Coding is indeed a nine-to-five job; nine pm to five am.
__________________
Coding is indeed a nine-to-five job; nine pm to five am.
Reply With Quote
  #2 (permalink)  
Old August 31st, 2006, 11:56 AM
Registered User
 
Join Date: Aug 2006
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Relationships? What's that? :D

I've never set them up between two tables.
Reply With Quote
  #3 (permalink)  
Old August 31st, 2006, 12:59 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

I can remember thinking the very same thing, blah, pointless normalization. At that time, I truly believed that there was not a significant speed increase and not one concrete reason on why I should normalize or setup relations between tables... Boy was i wrong.

Since you have studied normalization I will spare you on the differences between 1-1 and 1-Many relationships between fields, but ill give you a concrete example =]

Assume for a second you are a DBA in a health care facility that needs a database built for a client record management system. (I am just going to focus on a very narrow view of what would be needed in a real record management system)

Ok so you start out designing the database, sweet.

Well you need a clients table obviously, you need somewhere to store all of that data! Well what goes into that table? Identifiable information? Address information? Telecom Information?

Well break down the elements of a client and you come up with this:
Name
Address
City
State
Zip
SSN
DOB
Nationality
************
Gender
Phone Number
AgencyID
etc.

Ok so thats ONLY 12 elements, or columns in our table, no big deal. Actually it is. The only unique value I can see from that list would be the AgencyID (since most health instutites aren't in the business of reusing ID's), unique id's become very important in Nomalization/Relationships.

Anyway.

I would break the inital clients table down into something like this:
pk (int auto increment)
first name (varchar)
last name (varchar)
middle name (varchar)
DOB (varchar)
ssn (varchar)
gender_fk (int)
ethnicity_fk (int)
nationality_fk (int)
agencyID (int)

Ok now why would I do this? Whats up with the _fk extensions? Let me explain. Normalization states that a table should not containt duplication. So to prevent duplication we would break our address's off into seperate tables, along with the ethnicity, nationality, and gender. Why?

Because including these fields would create duplication, you could have 5000 rows where people have a United States nationality but, based on normal form, we have to elimiate the repition of the nationality: United State. Instead a row for a client may contain the value of 1 for their nationality and in the nationality table, the pk for United States would be 1. This prevents redundancy for our nationality and the same methodology applies for ethnicty and gender. (Lastly consider if the nationality of the United States changed to Inhabitiant of the Nation Formely known as the USA, sure you could call an update statement, but in a seperate table you would only have to change the value once)

By design, this client table should never change. Your DOB doesnt change, nor your ethnicity. In a very rare case your nationality may change but, over the course of the years, you can have multiple address's. So we break a clients address information out into a seperate table and it is related not by the pk value fo the client table, but by the AgencyID. Remember, this should ALWAYS be unique, never any duplication.

(Sure the pk field is the primary key and should always be unique and when you have only 2 tables it would be fine to relate on this, but in all reality, a database for this application will have quite a few tables that where all of the rows, in one way or another relate to a client; you can see why the relationship should be based on the AgencyID)

In the address talbe you would set up something like this:
pk (int auto increment)
agencyID_fk (int relates to consumer table)
address (varchar)
city (varchar)
state_fk (int)
zip (int)
history (bit)

This is a case of 1 to many, a client can have multiple address's over the course of the years BUT they will have only one CURRENT address, hence the history column. From here is a simple join based on the agencyID to retrieve the client address information.

From reading this, I don't suppose I have answered your question to well, just reguratated a bunch of Normalization methodologies and such so allow me to answer it in plain english.

This may seem like a lot of work, it may seem like a pain, but from a management stand point it is much less of a headache to maintain and your statements will be MUCH MUCH faster. Consider this: Say we didnt split up the client and address table and you have 5000 clients who each change their address 10 times that means you have 50000 records in one table, this takes a tremendous amount of time to retrieve. And say that you arent interested in the address's just the client SSN's, well now you have to do a SELECT DISTINCT (as opposed to a normal select) because you have duplication in your table (because of the mulitple address) AND it is going to take just as long to go through those 50000 records.

As I said at the begining of this post, I never saw the point of normalization until I got my first serious programming job and then it all became very clear to me why one would want to do this. Yes your syntax becomes a little more complex, yes you have tables that may only have 5 or 10 rows in them, yes it can be a pain spliting that information up, and yes it may be a pain trying to do a mock up of the database design but, the positives here far outweigh the inital headaches, and your users (especially the programmers) will love you for it.

Thoughts, comments?

"The one language all programmers understand is profanity."
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
displaying relationships williamlove Access VBA 1 August 14th, 2008 10:20 AM
1 to 1 relationships scandalous Access 13 January 31st, 2007 12:07 PM
Many-to-Many Relationships in O/R Mapping sarosh Classic ASP Databases 0 December 8th, 2006 05:45 AM
Table relationships tanjuakio SQL Server 2000 3 July 28th, 2005 09:14 AM
contraints and relationships zouky ADO.NET 11 September 24th, 2004 01:30 PM



All times are GMT -4. The time now is 08:38 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.