Child Of A Junction Table

[nanscombe]

@Nanscombe: I don't know if yours was a genuine question, or it was just out of concern for my example, but we don't actually keep records of our customers. We are a small retail business and I just wanted to help Dad handle the shop easily. What I'm hoping to achieve is to have each employee ‘sign in’ and so each transaction from then on will have an EmployeeID filled according to whomever is managing the till. Each Transaction has a timestamp so it's impossible to have two transactions overlap, as well

That's Ok.

I was thinking about it as an order tracking system for larger organisation.

it sounds as though you'll be writing an EPOS (Electronic Point of Sale) type of system.

Still, you may come across some similar scenarios if you intend to track stock orders from suppliers.

 

[stopher]

But what happens when you get a second transaction between a particular pairing of EmployeeID and CustomerID?

How do you differentiate between the different Transactions and then link its Note without either another field or by using a surrogate key?

EmployeeID and CustomerID alone would not be sufficient.

Agreed. I guess I'd got hung up on this table being a "junction" table which is normally used to describe a table that resolved a many:many relationship - hence two fields. But this is really a table of facts in its own right i.e. a table of transactions which I'd overlooked. And I can't see how "transactionAmount" could possibly be used as part of the key. So yes I agree a surrogate key is the only way to go. I would normally expect to see TransactionID as the PK of a transaction table and certainly not a composite key.

Regardless, I would still not split the 1:1 into separate tables.

 

[nanscombe]

Yes the "Note" field could have been placed in the table in this case.

However, yes there always seems to be a however from me, on other systems I have split the notes into a separate table. The ability to add notes (in memo fields) was required on several different places.

What I did was create a single entry form, accessed from a button, and pass it the PK of the parent record and a value to represent what "type" of note it was, eg "A" - note on a person, "B" - note on a transaction etc.

The form would then automatically populate a pair of non-visible fields with values that had been passed to it, as well as its own generated Primary key, as the record was saved.

When it came to reporting I used a couple of queries, based on tbl_Notes, which filtered out the type of note I was interested in at the time.

 

[Balthazar]

Some of you may be pleased to know that I've scrapped the child table and am just adding the Notes field, as is, to the junction table.

If it helps anyone with their projects, this is how the database is structured wrt to the transactions:
tblTransaction: { TransactionID (PK), TransactionDate, etc. }
tblItems: { ItemID (PK), ItemInfo, etc. }
jctItemTransaction: { TransactionID, ItemID, TransactionAmount, etc. }

@Nanscombe: Yeah, I did that too with some aspects of the database! Append queries are magic. I actually use Append queries to read in the code from the barcode scanner, for the individual items.

 

[RainLover]

Some of you may be pleased to know that I've scrapped the child table and am just adding the Notes field, as is, to the junction table.

If it helps anyone with their projects, this is how the database is structured wrt to the transactions:
tblTransaction: { TransactionID (PK), TransactionDate, etc. }
tblItems: { ItemID (PK), ItemInfo, etc. }
jctItemTransaction: { TransactionID, ItemID, TransactionAmount, etc. }

@Nanscombe: Yeah, I did that too with some aspects of the database! Append queries are magic. I actually use Append queries to read in the code from the barcode scanner, for the individual items.

I have been busy and therefore have missed some things.

This Note field. You claim that you are going to add it to the Junction Table. Just check that table to see if you can run duplicates. Simply copy a Record and paste it into a new line. I would prefer to attach the Notes to a more applicable Table. Note that I do not have a copy of your Database because you posted your sample in either 2007 or 2010. If you could convert it to 2003 and repost I would be able to see what you have done.

I also see the mention of a One to One relationship. Is this really happening and if so why would you do that instead of simply adding an extra field.

 

[Balthazar]

"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship"
That's a good thing, surely? It means I can't accidentally add the same item twice, to the same transaction. There's a ProductTransactionQuantity field for that kinda situation.

But you're right, I should just keep an extra field. I was just a little nullophobic but the people here cured that

 

[RainLover]

"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship"
That's a good thing, surely? It means I can't accidentally add the same item twice, to the same transaction. There's a ProductTransactionQuantity field for that kinda situation.

But you're right, I should just keep an extra field. I was just a little nullophobic but the people here cured that

I very much douubt that you have a One to One Relationship.

If in doubt look at your Relationship Window. The join lines are different. It will have a One on each end of the joining line.