Child Of A Junction Table

[Balthazar]

Hi, I hope you're well. I'm new to these forums and have an issue I hope you can help me with.
Suppose you have 2 tables:

• tblEmployee (PK: EmployeeID)

• tblCustomer (PK: CustomerID)

[Attached as zip file]

Each employee can sell to many customers, each customer can purchase from many employees. A junction table can be used to represent each transaction:

• jctEmployeeCustomer (PK: EmployeeID, PK: CustomerID; EmployeeCustomerTransactionAmount)

Now suppose you want to be able to add a Note to each transaction. You can add a column that says EmployeeCustomerTransactionNote, but there will be a lot of empty fields, for every EmployeeCustomerTransaction that didn't require a note.
Herein lies my problem. With a normal table, I would have made a child table with a FK and a Note field. But there's no single PK for the junction table. Do I keep a lot of empty fields, and break normalisation rules, or is there anything else you can suggest?
Again, I would sincerely appreciate any help.

Edit: I forgot to mention, there's a form produced that would be used to record transactions. How would you advise for, or against, producing another junction table (jctEmployeeCustomerTransactionNotes: PK: EmployeeID, PK: CustomerID; EmployeeCustomerTransactionNote) and having a button on the form that, when clicked, produces a message box and runs an Append query, using VBA, to inject the note into the new junction table?

 

[MarkK]

Hey, welcome to the forum

Create a unique primary key in the junction table, (You've created a primary that is the composite of the two foreign keys) and use that to join the child records of the junction table.

 

[Pat Hartman]

I generally don't create surrogate keys for junction tables UNLESS, the junction table will have child tables as in your example. Then I add the autonumber PK in addition to the unique index on the relation fields.

 

[MarkK]

Pat, why wouldn't you create a surrogate key in a junction table? Is this personal preference or is there an argument you would make for why you do it that way? Thanks,
Mark

 

[RainLover]

Pat

Just for clarification, you do mean the the Autonumber is the PK not all three Fields combined. I may just be reading you incorrectly but it is worth mentioning.

Cheers

 

[Cronk]

I don't see any need for, and would not have a child table, which presumably only contains one data field (in addition to FK which can be the PK unless there are many separate notes for each transaction).

Incidentally there won't "be a lot of empty fields" as the original post quoted but rather one field which will have a lot of Null entries.

This does not break any normalisation rule nor adds to the size of an Access file.

So I would just add a notes field to the many-to-many Join table.

 

[Pat Hartman]

@Rain, Autonumbers are unique so adding additional fields to make a composite PK doesn't make it more unique. Autonumbers stand alone. They are never combined with other columns in a PK. The original composite PK should now become a unique index instead because you still need to enforce uniqueness on the combination but you can't do that by including the autonumber since the autonumber is unique in and of itself. The reason for adding a surrogate key when you have a natural candidate key is convenience. In this case, specifically to work with a combo.

 

[nanscombe]

I don't see any need for, and would not have a child table, which presumably only contains one data field (in addition to FK which can be the PK unless there are many separate notes for each transaction).

A unique ID for the transaction would probably be useful from a tracking point of view.
I can think of a couple of other field the OP might wish to consider:
Transaction date.
Transaction method - Cash, Credit card, Cheque etc.
How about a status field? Possible values (off the top of my head); Pending, processing, dispatched, returned.

As for notes, what if there happens to be more than 1 note for a transaction?

1) Initial contact hh:nn dd/mm/yyyy
2) Check with dispatch hh:nn dd/mm/yyyy
3) Contact client hh:nn dd/mm/yyyy
4) Spoke with manager hh:nn dd/mm/yyyy
5) Contacted client again hh:nn dd/mm/yyyy
...

Plus the client may be speaking to different people each time.

Theoretically there could be an entire conversation taking place. It could be placed in a single memo field but you would have to be very careful about maintaining the integrity of what was written, or overwritten.

 

[Balthazar]

Thank you all for your replies, there is some fantastic discussion happening here.

I ended up opting for Lagbolt/Mr Hartman's suggestion of an autonumber PK field, which links to the child table holding the notes. So the junction table ultimately now has 3 PK's: the 2 composites and the autonumber. It seems to work.


@Nanscombe: I just whipped up this example, to understand how I could tackle this problem, but this is actually part of a bigger Point-of-Sale/Inventory/Accounts database. I like the idea of a status field though. Thank you


@Cronk: I appreciate what you're saying, and I see a lot of merit in it, but it just feels ‘cleaner’ with a child table. Null values make me sad.

 

[RainLover]

Thank you all for your replies, there is some fantastic discussion happening here.

I ended up opting for Lagbolt/Mr Hartman's suggestion of an autonumber PK field, which links to the child table holding the notes. So the junction table ultimately now has 3 PK's: the 2 composites and the autonumber. It seems to work.


@Nanscombe: I just whipped up this example, to understand how I could tackle this problem, but this is actually part of a bigger Point-of-Sale/Inventory/Accounts database. I like the idea of a status field though. Thank you


@Cronk: I appreciate what you're saying, and I see a lot of merit in it, but it just feels ‘cleaner’ with a child table. Null values make me sad.

A technical point.

A Table can only have ONE primary key.
Your PK is the Autonumber.
The other two fields may be unique etc They most likely are indexed but they are not a Key and should never be thought of as one.

 

[ButtonMoon]

A technical point.
The other two fields may be unique etc They most likely are indexed but they are not a Key and should never be thought of as one.

Hi Rain,

A key is a (irreducible) set of attributes that are mandatorily unique and non-nullable. Of course you are right that only one key per table is called primary key but the others are known as secondary keys or alternate keys. They are no less important as keys though. For example normalization is concerned equally with all the candidate keys of each relation and not just any one of them. It's certainly an important aspect of any database design that all such keys should be properly identified - so I recommend that any careful database designer should be thinking of them that way! Maybe that clarifies what you said or meant to say.

HTH.

 

[ButtonMoon]

Herein lies my problem. With a normal table, I would have made a child table with a FK and a Note field. But there's no single PK for the junction table. Do I keep a lot of empty fields, and break normalisation rules, or is there anything else you can suggest?
Again, I would sincerely appreciate any help.

Here's a problem of terminology that's been discussed in these forums before - with, I think, no very satisfactory conclusion: What is a junction table?

The best definition I've found is that a junction table is a table with more than one foreign key in it. If you accept that definition then I don't think you need to worry unduly about doing anything differently for tables with more than one foreign key. Why should it make a difference how many foreign keys a table has? If you don't agree with that definition then maybe it would help if you could explain what you think a junction table is.

Just my 0.02.

 

[RainLover]

Hi Rain,

A key is a (irreducible) set of attributes that are mandatorily unique and non-nullable. Of course you are right that only one key per table is called primary key but the others are known as secondary keys or alternate keys. They are no less important as keys though. For example normalization is concerned equally with all the candidate keys of each relation and not just any one of them. It's certainly an important aspect of any database design that all such keys should be properly identified - so I recommend that any careful database designer should be thinking of them that way! Maybe that clarifies what you said or meant to say.

HTH.

I said what I wanted to say. I don't need you to tell other people what I meant to say.

 

[RainLover]

ButtonMoon

I have just searched Microsoft and in particular the Help files.

I have not found one single reference to any of the following. "secondary key", "alternate key" or "candidate key.

Do you have a particular agenda to change Microsoft's terminology and if you do why would you want to do that. Wouldn't it be better to stick to the standard that Microsoft introduced over 20 Years ago and has served us well all these years.

 

[ButtonMoon]

ButtonMoon

I have just searched Microsoft and in particular the Help files.

I have not found one single reference to any of the following. "secondary key", "alternate key" or "candidate key.

Do you have a particular agenda to change Microsoft's terminology and if you do why would you want to do that. Wouldn't it be better to stick to the standard that Microsoft introduced over 20 Years ago and has served us well all these years.

Microsoft's terminology? Relational database concepts (relations, keys, foreign keys, etc) were invented before Microsoft even existed. I hope no professional person relies on Microsoft help files to learn about relational database design principles because as far as I know that kind of education is not a part of Microsoft's mission. If the OP isn't familiar with the fundamentals of data management and database design then he would surely be well advised to take a course or study a good book and not waste his time reading Microsoft help files.

 

[RainLover]

Microsoft's terminology? Relational database concepts (relations, keys, foreign keys, etc) were invented before Microsoft even existed. I hope no professional person relies on Microsoft help files to learn about relational database design principles because as far as I know that kind of education is not a part of Microsoft's mission. If the OP isn't familiar with the fundamentals of data management and database design then he would surely be well advised to take a course or study a good book and not waste his time reading Microsoft help files.

waste his time reading Microsoft

So now you are claiming that you know better than Microsoft.

I assume you do have a rest once a week.

 

[nanscombe]

Microsoft Developer Network - Table Keys

The primary key is composed of up to 20 fields in a record. The combination of values in fields in the primary key makes it possible for the DBMS to perform a unique identification of each record. The primary key determines the logical order in which records are stored, regardless of their physical placement on disk.
...
A secondary key uses an additional structure that is called an index. This is similar to the idea of an index that is used in textbooks. A textbook index alphabetically lists important terms at the end of a book. Next to each term are page numbers. You can quickly search the index to find a list of page numbers (addresses), and you can locate the term by searching the specified pages. The index is an exact indicator that shows where each term occurs in the textbook.

Microsoft Developer Network - Constraints

A table may have more than one combination of columns that could uniquely identify the rows in a table; each combination is a candidate key. The database administrator picks one of the candidate keys to be the primary key. For example, in the part_sample table both part_nmbr and part_name could be candidate keys, but only part_nmbr is chosen as a primary key.

 

[ButtonMoon]

waste his time reading Microsoft

So now you are claiming that you know better than Microsoft.

I assume you do have a rest once a week.

Rain, I don't claim any such thing. I said it's not Microsoft's mission or practice to teach relational database design principles and that's not the place any wise person ought to go to learn about it. There are plenty of people I know at Microsoft who know as much or more about database design as I do. I can promise you they all know what a candidate key is! Are you seriously suggesting that the terms and concepts used and understood by millions of database professionals and taught in educational institutions around the world are wrong just because you can't find it in a Microsoft help file! I can't believe you would seriously take such an absurd position but then I really don't know what your problem is.

 

[RainLover]

ButtonMoon

There are plenty of people I know at Microsoft who know as much or more about database design as I do

I will sleep better knowing that there are at least some people at Microsoft who know as much as ButtonMoon.

I now understand you position towards Microsoft and why people should ignore the help files written by these people.

 

[jdraw]

Thanks ButtonMoon. There seems to be a lot of people who have never heard of Peter Chen or Ted Codd or Chris Date and will continue to remain oblivious to the history, development and fundamental concepts of data base, data modelling and data administration. Life for them seems to begin and end with M$oft. It's quite amazing that some see database = Access . It seems they do not see nor accept that M$oft has taken some database concepts and techniques and packaged/implemented these as a commercial software product named Access. Or another commercial implementation as Sql Server.

Here is a Microsoft quote that at least gives some recognition to Chen.

Entity Data Model

..

The Entity Data Model (EDM) is a set of concepts that describe the structure of data, regardless of its stored form. The EDM borrows from the Entity-Relationship Model described by Peter Chen in 1976, but it also builds on the Entity-Relationship Model and extends its traditional uses......