Question Does a every table in a 3NF DB have to have a PK? (1 Viewer)

[oZone]

Does every table in a 3NF DB have to have a PK?

I'm an access noob, still learning, please bare with me.

When building a database in Third Normal Form, does every table in the DB have to have a primary or composite key? Can a table just have a Foreign key and no Primary key?

I should know this, but for some reason my brain isnt working at the moment...

Thanks!

 

[Banana]

Here's series of question.

How do you intend to uniquely identify a given row from the table?

What would be the most efficient manners of uniquely identifying the given row? How many columns, and what data types should we use?

The speed of index depends on the size of data necessary to point back to the actual row. How small can we get this?

Hopefully that will help you think things through. Post back if you have any more questions.

 

[oZone]

Well, here's my specific situation. I have a table, and the part in question is structured like so:

tblClients (ClientNumber, ClientName, ClientType)
tblClientContacts (ClientNumber, Phone, FirstName, LastName)

The ClientNumber in tblClients is the PK, and I need to join it to tblClientContacts but do I need to put a PK in tblClientContacts? Cant I just join the PK in tblClients to the FK in tblClientContacts?

The thing is, I cant make the ClientNumber field in tblClientContacts a PK because several clients have more than one contact, which creates duplicates of the same ClientNumber in that column. And I am not allowed to create any additional fields in any tables (such as ID fields), I have to work with only the fields that were provided in the original FlatFile table (this is a 3NF normalization exercise). So rather than create a composite key, wouldnt it be easier to leave a PK out of tblClientContacts and just use the ClientNumber as the FK?

 

[Banana]

You need to use a foreign key to model the one-many relationship.

tblClients
ClientNumber <- The primary key

tblClientContacts
ContactID <- The primary key for the tblClientContacts table
ClientNumber <- Foreign key.

The relationship would be from tblClient.ClientNumber to tblClientContacts.ClientNumber, and the tblClient's ClientNubmer would be an autonumber, while the tblClientContacts' ClientNumber is just a Long Integer.

Note that you do not 'specify' a foreign key as you do with primary key in table design view. This is implied when you create a relationship between the two fields and Access will know that the ClientNumber in the tblClientContacts is a foreign key.

HTH.

 

[boblarson]

If it were me, I would have it set like this:

tblClients
ClientID - Autonumber (PK)
ClientNumber (optional unless you were doing the same as my clientID)
ClientName - Text
ClientTypeID - Long Integer (FK from ClientTypes table)

tblClientTypes
ClientTypeID - Autonumber (PK)
ClientTypeDescription - Text

tblClientContacts
ClientContactID - Autonumber (PK)
ClientID - Long Integer (FK from tblClients)
Phone - text
FirstName - text
LastName - text

 

[oZone]

Rite, but in my response to Banana I clarified by saying:

The thing is, I cant make the ClientNumber field in tblClientContacts a PK because several clients have more than one contact, which creates duplicates of the same ClientNumber in that column. And I am not allowed to create any additional fields in any tables (such as ID fields), I have to work with only the fields that were provided in the original FlatFile table (this is a 3NF normalization exercise). So rather than create a composite key, wouldnt it be easier to leave a PK out of tblClientContacts and just use the ClientNumber as the FK?

Although I appreciate you taking the time to respond to my post, your responses dont really provide a solution in my case...

 

[Banana]

Exactly why are you not allowed to add the fields to the table?

If you are importing this from a text file or something like that, then it's a matter of adding a column to act as the PK in the process of importing.

 

[boblarson]

Rite, but in my response to Banana I clarified by saying:


Although I appreciate you taking the time to respond to my post, your responses dont really provide a solution in my case...

Hmmm, you aren't allowed to design it properly? Who has this requirement in mind? By saying you can't add any fields to the one table, you are saying that you can't design the database properly and can't make a proper application. Sorry to be so much a stickler on this but if someone is forcing bad design on you I would be telling them to design and build it. I wouldn't be building a shoddy product and putting my name on it.

 

[oZone]

I understand it sounds strange or confusing. Please allow me to explain.

It's not that I am not allowed to design it properly, it is a learning assignment focused on teaching only the concept of normalization by using a fictional database not ever intended to be used in a real-life situation.

The instruction was "Don't add features because you may think it is a better client support system. i.e. if it doesn't break a normalization rule then leave it alone."

I was asked to take an Excel document which contained client and tech information, import it, then from that imported table create a database that is normalized to Third Normal Form, without adding any additional fields not included in the original imported database.

As I learn more and more about database design, the excersises will each focus on a new concept that I havent learned yet. For now I am on the subject of Normalization.

I hope this helps you understand.


I've included the original FlatFile DB below so you can see what I have to work with, as well as my attempt at 3NF without being able to add additional fields...

 

[Pat Hartman]

The instructor said to not add features. He didn't say to not add columns. I would add an autonumber column if I didn't have a candidate PK. Every table in a proper schema needs a PK. The principles are different for data warehouse schemas since those tend to be slightly flattened but that is not what you are working on now.

 

[oZone]

My instructor also said "You don't really need to add any additional fields to solve this."

So I'm wondering if I'm going about the CustomerContacts table wrong? I just dont see any other way of assigning a PK to this table other than making a composite key with the CustomerNumber and either LastName or PhoneNumber...

If you were to do a composite key, what would make more sense to use? CustomerNumber and LastName, or CustomerNumber and PhoneNumber?

 

[boblarson]

My instructor also said "You don't really need to add any additional fields to solve this."

Not that you wanted to hear this, but then I question your instructor's knowledge of Relational Database Management Systems.

So, I guess you might need to do it the way they suggest but realize that it isn't the correct way for future use.

So I'm wondering if I'm going about the CustomerContacts table wrong? I just dont see any other way of assigning a PK to this table other than making a composite key with the CustomerNumber and either LastName or PhoneNumber...

If you were to do a composite key, what would make more sense to use? CustomerNumber and LastName, or CustomerNumber and PhoneNumber?

For your purposes, you can get by with either. However, just be aware that this is not the correct way to do it, even though it is the way you've been told you should do it (and that you must do it that way because the instructor has the power to pass or fail you).

So, I guess you gotta do what you gotta do, eh? So based on the funky requirements you have, then I think you are okay to proceed.

 

[Banana]

First, you don't store first name and last name in contacts table. That should stay in Client table. If that was given to you, I can hope that they meant names to ask for when calling that phone number or something like that.

Second, you could create a composite key with ClientNumber + PhoneNumber, which will give you unique pair (e.g. no client will have same phone number twice, but can have several different phone numbers and share phone number with different clients).

 

[boblarson]

First, you don't store first name and last name in contacts table. That should stay in Client table. If that was given to you, I can hope that they meant names to ask for when calling that phone number or something like that.

Actually, I would disagree Banana. The Client table would be for the client (which could be a business name). Then the CONTACTS table is a list of all contacts at that client. So, it depends on your definition and use of CLIENT.

 

[oZone]

Thanks Bob. I realize now that his instruction may be strange, but there really isnt anything I can do but do what he requires... at least until I pass the class (I've got 100% so far... so even if I get docked a little on this one, it shouldnt hurt me too much).

Thanks for helping me.

First, you don't store first name and last name in contacts table. That should stay in Client table. If that was given to you, I can hope that they meant names to ask for when calling that phone number or something like that.

Bannana, the clients have business names, which are kept in the clients table. The Contacts table is a collection of the contact persons and their phone numbers for the business.

you could create a composite key with ClientNumber + PhoneNumber, which will give you unique pair (e.g. no client will have same phone number twice, but can have several different phone numbers and share phone number with different clients).

Thanks for the advice, I think I'll do that.

 

[oZone]

I just got wind of my Final project. It will be to create a full DB for a fictitious video store. It will consist of forms, reports, and most likely need to be in 3NF. Since I am a "movie buff" it should be a fun project.

I'll definately be asking more questions on the near future.

For now, I have one more assignment before the final. I'll be studying reports this next week. So I hope I dont bug you guys too much, but I really need all the info I can get.

Thanks for the help again, it's been a huge help!

 

[Banana]

Glad to see I was wrong about the first/last names in the table. Goes to show that there's always a different approach and reasons.

Good luck with the assignment.

 

[oZone]

Hi, just one more question... I wanted to make sure I'm putting the correct fields in the Clients table to meet 3NF requirements and am questioning the "Billed" and "Paid" fields.
Originally I had them in a seperate Billing table, but after thinking about it and after seeing some other examples that show billing information in client tables, I decided to move them back into the client table.

Here is a screeny of the table as it is now:

My goal, again, is not to design a totally user friendly DB in this assignment, but to prove that I know how to put a DB in 3NF. Considering this, is the Clients table in 3NF with these billing fields in it?

 

[Banana]

Let's try this:

How many times could you get billed?

And how many times would you pay a given bill?

Then, what are the chances that the answers you gave for first two questions will not apply, and how?

As you can see from my earlier blunder with definition of Last Names in contacts table, how do you then define 'paid' and 'billed'?

HTH.

 

[The_Doc_Man]

It is rare that I contradict Pat and I usually duck afterwards. But sometimes I have to point out a fine point in the issue of pragmatic design.

At least in theory it is not necessary to have a PK on every table. It has very serious implications to say that, though. Pat is absolutely right to say it is not recommended. The reasons have been touched on in earlier posts.

In a one-to-many table, the one side ABSOLUTELY must have a PK. Without it, you have no relationship. Period.

If the many-side table doesn't have a PK, it means you cannot so easily select a single row from the many-side table for an update of that one row. But if it at least has an FK corresponding to the right PK from the one-side table, it will work in parent/child queries (or parent/child forms) and still will be normalized. Relational integrity could be declared and would work.

Now, if the first many-side table is ITSELF a parent in its own one/many setup in a three-tier cascade, then YES the "middle" table ALSO needs a PK as a normalization issue. And in that case, the grandchild table is the ony participant that could get by without a PK. With, as noted, the same restriction on selective updates and the same issues regarding relational integrity.

There IS such a thing as a compound PK such that the FK and some other field in combination have to be unique. The many-side table can have a valid compound PK comprised of the FK and something else. Hint: The shortest possible "something else" that retains uniqueness would be the best choice. But it MUST retain uniqueness when taken in combination with the FK. AND in fact, if such a candidate exists without the FK as a compound key participant, that's more better, I gawrontee! (sorry, lapsed into my Cajun accent...)

Now, here is the issue that would trump everything your instructor said. IF you have a requirement to be able to selectively update a single record from the many-side table and have no candidate keys for the compound PK, add the ID field as an issue in program design necessity, because in that case, you have a requirement that dictates the presence of a PK.

Here's why: The issue of a unique update requirement implies that there will be some record in that table that is uniquely identified by a PK, whether single or compound. Which IS a normalization issue, because that ability to uniquely identify a record based on its PK is another way of saying that all fields in a given record depend on all keys of that record and nothing else. This is a property-based definition rather than an "eliminate xyz by moving to another table" definition. And THERE is your loophole. Note that if a unique update of the many-side table's records is NOT required, then you do not need that PK.