Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access 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
  #11 (permalink)  
Old March 26th, 2008, 11:44 AM
Friend of Wrox
 
Join Date: May 2006
Location: San Diego, CA, USA.
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by dparsons
 I think this is one of those topics where you are bound to have varying degress of opinions but I am always up for a well educated discussion.

While Woody makes the argument that City is reliant on the Primary Key value in the table I would tend to disagree. Can't you make the argument that its a transitive relationship because the city (presumably the city where the person lives) relies on the FullName which then relies on the Primary Key?

Further I think the overall question is from a practical standpoint (e.g. application development) what is functional. I am sure Codd would utterly rip me apart if he saw some of my Database designs but there comes a point that if you normalize to far your application preformance can suffer because of the Joins necessary to get a "complete" record.

-Doug

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========
First of all, I disagree that "this is one of those topics where you are bound to have varying degrees of opinion". How's that for a varying degree of reality? Seriously - I think we'll get to some level of agreement through this process.

It is hard having a discussion through forum posts, however.

So... to that end, I don't see how the city is dependent on the FullName at all. The city is dependent on the Customer, and the customer is identified by the CustId. However - as in my most recent previous post, there are anomolies that can occur if we store the city NAME in the table because the name itself is not dependent on the customer entity at all.

Woody Z
http://www.learntoprogramnow.com
How to use a forum to help solve problems
My blog... please visit
Reply With Quote
  #12 (permalink)  
Old March 26th, 2008, 11:47 AM
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

Woody is quite right williamloveI. You need to first agree what column is the Primary Key and not available for duplication. In your example you said:

"But if the record for a given CustID is changed so that the same CustID is used, but a different person with a different name is used, then in general the city would have to change. So in any case other than a person changing their name, City is dependent on FullName and this violates 2NF. "

This table wouldn't even be in 1NF if this were the case because, by definition, you need a unique key column that does not allow for duplication to be in 1NF. So if the ability to have dupe ID's exist, the table simply doesn't conform.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========
Reply With Quote
  #13 (permalink)  
Old March 26th, 2008, 11:53 AM
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

Quote:
quote:Originally posted by woodyz
 
Quote:
quote:Originally posted by dparsons
Quote:
 I think this is one of those topics where you are bound to have varying degress of opinions but I am always up for a well educated discussion.

While Woody makes the argument that City is reliant on the Primary Key value in the table I would tend to disagree. Can't you make the argument that its a transitive relationship because the city (presumably the city where the person lives) relies on the FullName which then relies on the Primary Key?

Further I think the overall question is from a practical standpoint (e.g. application development) what is functional. I am sure Codd would utterly rip me apart if he saw some of my Database designs but there comes a point that if you normalize to far your application preformance can suffer because of the Joins necessary to get a "complete" record.

-Doug

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========
First of all, I disagree that "this is one of those topics where you are bound to have varying degrees of opinion". How's that for a varying degree of reality? Seriously - I think we'll get to some level of agreement through this process.

It is hard having a discussion through forum posts, however.

So... to that end, I don't see how the city is dependent on the FullName at all. The city is dependent on the Customer, and the customer is identified by the CustId. However - as in my most recent previous post, there are anomolies that can occur if we store the city NAME in the table because the name itself is not dependent on the customer entity at all.

Woody Z
http://www.learntoprogramnow.com
How to use a forum to help solve problems
My blog... please visit
Agreed, having a good discussion through a Forum is a bit tricky but alas, here we are! ;]

I agree with your statement that a City in and of itself is related to a customer and not necessarily to the full name field since it is quite likely you could have duplicate names (John Smith for example) in this table. So yes, to reiterate your point, you could store the city value in this table although not the city Name.

(Look at that we have come to an agreement!)

Good to see you BTW, you haven't been around much lately.

-Doug

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========
Reply With Quote
  #14 (permalink)  
Old March 26th, 2008, 12:44 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Naperville, IL, USA.
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

One important thing to remember is that city name must be based upon a composite key of city name and state. Otherwise, there will be problems with the same city name in different states. As two examples I would suggest "Springfield" and "Jackson".

Rand
Reply With Quote
  #15 (permalink)  
Old March 26th, 2008, 02:48 PM
Friend of Wrox
 
Join Date: May 2006
Location: San Diego, CA, USA.
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hey Doug,

I haven't had much time for posting lately. How do you get time to do so much? Do they pay you to hang around here?

I've been mostly working too much, and continuing to do presentations at various conferences and user groups.
This next week I'm presenting on using NMock with C# to do exploratory characterization tests on legacy code. Sound like fun?

Anyway - it's nice to see you are still around.

Have fun...
Woody Z.


Woody Z
http://www.learntoprogramnow.com
How to use a forum to help solve problems
My blog... please visit
Reply With Quote
  #16 (permalink)  
Old March 26th, 2008, 02:57 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

Ahh to be paid to post on p2p now that would be nice. I always find time through the course of the day to post a comment here or there but mostly when I am home at night working on one thing or another.

Look on the bright side, at least you have Conferences and User Groups to attend, NE Ohio is lacking in both areas. =\ Se la vi.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========
Reply With Quote
  #17 (permalink)  
Old March 30th, 2008, 12:29 AM
Authorized User
 
Join Date: Mar 2008
Location: , , .
Posts: 20
Thanks: 1
Thanked 0 Times in 0 Posts
Default

I now feel my example is weak and ambiguous. Let me ask my question using my real situation, which is a personal song and playlist database.

If there is a God (I happen to believe there is but that’s beside the point) and God assigned a unique number every time a music artist on earth recorded a song (and revealed the list) then we would have

tblSongs
-SongID (primary key)
-SongTitle
-Artist

I could word the scenario without mentioning God and just say that in the abstract, every time a song recording event occurs it could be mapped to a SongID, and I believe this abstraction is correct. If that is so, then I can go on. If you feel it is not true, then we will have to discuss why SongID is not necessary and sufficient as a primary key. I am eager to hear opinions on that.

If SongID is a necessary and sufficient primary key, I believe SongTitle is only dependent on SongID because this relationship is established the moment the song is recorded. I believe the same is true of Artist. Since both are only dependent on the primary key, they are mutually independent, and therefore the table is in 3NF.

My problem is that if I decide to change the spelling of the Artist or some other aspect of Artist (maybe putting a comma in somewhere), I have to edit many rows of the table. That seems a bit of a violation of the spirit of 3NF. I still believe the table is in 3NF. But clearly the table can be changed as follows:

tblSongs
-SongID (primary key)
-SongTitleID
-ArtistID

tblTitles
-SongTitleID
-Title

tblArtists
-ArtistID
-Name

I would like to know if my original table is, as I believe, in 3NF, and if so, what are the implications of the fact that I can modify the design with more tables?



Reply With Quote
  #18 (permalink)  
Old March 30th, 2008, 06:01 PM
Friend of Wrox
 
Join Date: May 2006
Location: San Diego, CA, USA.
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts
Default

As far as User Groups and Conferences go, I am lucky that there are a number of groups that meet within 15-20 miles of where I work (which is in North San Diego County). However, if I was in Ashtabula or Painesville I'd try to put a study group together. All you need is a few people interested in meeting monthly or weekly to help each other study on some topic (such as Design Patterns or ASP.NET). You can meet at a library, or in someone's office.

The Code Camp idea works great for a yearly get together. You only need enough people to put together a dozen or so talks on anything they are interested in, and you've got it started. Over a few years it can grow into something very rewarding both knowledge gathering as well as for meeting and getting to know people. Anyway - there have got to be enough people wihtin 100 miles of you to make something like that work.

Woody Z
http://www.learntoprogramnow.com
How to use a forum to help solve problems
My blog... please visit
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
design question androoo Classic ASP Databases 1 November 13th, 2004 09:41 AM
design question androoo General .NET 1 November 13th, 2004 09:40 AM
design question androoo .NET Web Services 1 November 13th, 2004 09:39 AM
Design Question flyin General .NET 4 July 21st, 2004 03:59 PM
Design question? qadeer05 SQL Server 2000 4 May 3rd, 2004 10:17 AM



All times are GMT -4. The time now is 07:20 PM.


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