Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 26th, 2008, 11:44 AM
Friend of Wrox
 
Join Date: May 2006
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
 
Old March 26th, 2008, 11:47 AM
Wrox Author
 
Join Date: Oct 2005
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
================================================== =========
 
Old March 26th, 2008, 11:53 AM
Wrox Author
 
Join Date: Oct 2005
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
================================================== =========
 
Old March 26th, 2008, 12:44 PM
Friend of Wrox
 
Join Date: Jun 2003
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
 
Old March 26th, 2008, 02:48 PM
Friend of Wrox
 
Join Date: May 2006
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
 
Old March 26th, 2008, 02:57 PM
Wrox Author
 
Join Date: Oct 2005
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
================================================== =========
 
Old March 30th, 2008, 12:29 AM
Authorized User
 
Join Date: Mar 2008
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?



 
Old March 30th, 2008, 06:01 PM
Friend of Wrox
 
Join Date: May 2006
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





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





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