Child Of A Junction Table

[RainLover]

Back on topic: i suggest ono to one relationship betwen notes table and junction table. Judging by the main discution in with you talk about a field in junction table, i guess that is only one note for each transaction.

A One to One Relationship will limit the Number of Records in the Partent Table to equal the Child table.

A One to Many is what you need, The Object of the Junction Table is to create a Many ro Many.

 

[Balthazar]

I finally tried testing this setup and I'm having some issues.

I highlight all three fields in the Design view of the junction table and click the Primary Key button at the top. However, when I try to make a relationship between the Autonumber PK in the junction table and the Number PK in the child table, Access forces me to make a relationship from the child to the junction table. Furthermore, it gives me a message about 'invalid field definitions' but I suppose that's because it's just trying to go from the child to the junction.

Please advise.

Edit: After some googling, I've realised that when I highlight the 3 keys I'm actually making one big composite key (I think) instead of two composites and a primary. That's why Access think I'm linking the child to the junction, because that's how the composite should behave. Still don't know how to fix it, though..

 

[RainLover]

If you get rid of the Idea of using composite keys you will find life easier.

Once you get a better understanding then play with composite keys. As you may have guseed I hate using them. However you need to know how to use them if you find cause to work on someone else's Database.

I will attach a Sample Database that has samples of Many to Many Relationships.
Look at the Relationships then try out every form. Not all Forms/SubForms are Many to Many.

EDIT

Attachment removed due to errors.

 

[ButtonMoon]

In contrast to Rain's reply, I'd like to state an alternative point of view that composite keys are not only useful and desirable but virtually an essential element of effective database design. You cannot afford to ignore, discard or fail to enforce a key just because it happens to be composite.

It's true that many database designers do prefer to avoid using composite foreign keys, and I acknowledge that there are sometimes good reasons to avoid using composite foreign keys. That reason alone does not stop you implementing composite keys however. A composite can be a key without also being used as a foreign key. If you do decide to use an alternative simple key (i.e. non-composite) then that doesn't obviate the need for a composite in the same table as well because the composite key may still be just as important for data integrity reasons, as an identifier or because some business logic has to rely on its uniqueness.

Usually a composite key should be supported by a unique index (normally required to eforce uniqueness by most DBMSs including SQL Server, and also in Jet/ACE) but it may or may not be a primary key.

 

[nanscombe]

Of course, for a composite key to be useful it has to be unique, ie only one possible record with that combination.

As soon as you start adding other fields like amounts or dates which could give you the possibility of having more than one record with the matching combination of fields the key ceases to be unique and so can't be used as a Unique / Primary key, but could still be useful as an Index for sorting.

It would probably not be sensible to use amounts, or dates / times as part of a key unless you could guarantee that:

a) they would never be duplicated, as part of the combination of fields
b) they would never be changed.

So if you have the possibility of having multiple records with the same groups of fields, excluding amounts or dates, then you will probably have to use something else, ie an autonumber or generated field, as your key.


In the OPs case the table with the fields EmployeeID & CustomerID could not be considered as unique because there may be more than one order from a particular customer placed with a particular employee.

If there were a repeat order it may even be for the same amount, so that couldn't be used.

If the customer was a big company theoretically they could make more than one order, with the same employee, on the same date so that wouldn't be unique either.

 

[catalin.petrut]

A One to One Relationship will limit the Number of Records in the Partent Table to equal the Child table.
A One to Many is what you need...

Why do you consider that notes are needed in a one to many relationship?
Before you answer, let me say why i think that one 2 one relationship is my recomandation. The client has a junction table for transactions. Once in a while a transaction has a note attached. The question is: should be a field in junction table for the notes or should be another table for this notes?
Based on developer idea, he doesn't want to many empty note fields on tha junction table. That is why i suggested another table, ono to one related with transactions table. One to one because for a transaction can exist only one note.

Waiting for your explanation. Maybe i dont understand something.

 

[Balthazar]

Mr. Wells, I hope I'm not over-stepping but I think I love you. Thank you, thank you and thank you. That example database was perfect to help me understand not only how and where composite keys should (and more importantly should not) be used but also to help me understand what a Primary key is. I was fixated in the idea that every table should have a primary key, because otherwise the table is naked, but your example really gave me an intuitive understanding.

What finally worked for me was to:
Not use the EmployeeID and CustomerID as PKs but rather FKs and the EmployeeCustomerID as the PK which meant I could have a child, too.

I'm going to mark this as solved now, with the greatest hopes that I don't have to unmark it but your responses and help have been greatly appreciated. Thank you

 

[RainLover]

I am pleased that you finally understand.

Remember the old statement KISS.

As I said if you use Autonumber you cannot go wrong. If you go the other way you can get confused and when it comes time to write complicated SQL Statements, disaster for the beginner.

Thanks for the acknowledgement.

 

[RainLover]

In the OPs case the table with the fields EmployeeID & CustomerID could not be considered as unique because there may be more than one order from a particular customer placed with a particular employee.

If there were a repeat order it may even be for the same amount, so that couldn't be used.

If the customer was a big company theoretically they could make more than one order, with the same employee, on the same date so that wouldn't be unique either.

Nigel

As you may have read I am anti composite Keys.

However if you look at my sample database I have, for demonstration purposes used EmployeeID & CustomerID as the Primary Key.

This appears to go against what you are saying. If you have the time could you give an opinion on this.

 

[stopher]

I will attach a Sample Database that has samples of Many to Many Relationships.
Look at the Relationships then try out every form. Not all Forms/SubForms are Many to Many.

I took a look at your database. Maybe I do not understand the basis of the model but some fundamental issue I see are:

1. You have set LastName/FirstName as the primary key in tblEmployeeTraining. It is generally advisable that PKs should not carry any real world meaning and names definitely should not be considered as candidate keys. Furthermore, they are not candidates for the primary anyway. It would imply a person can only be associated with an employee-training record once. Surely EmployeeTrainingPK should be the obvious PK in your design, otherwise EmployeeTrainingPK seems redundant.

2. You have not set any constraint on the FKs (EmployeeFK, TrainingTypeFK). So this means a given employee can have a given type of training many times. Is that really the intention? In which case it is hardly relevant to the OP's question.

3. Why is LastName/FirstName in two tables? Are you talking about the same person or not? Data should not be duplicated in different tables (although I don't know if you refer to the same person).

Chris

 

[nanscombe]

Nigel

As you may have read I am anti composite Keys.

However if you look at my sample database I have, for demonstration purposes used EmployeeID & CustomerID as the Primary Key.

This appears to go against what you are saying. If you have the time could you give an opinion on this.

I am getting confused. Your sample database, the same one that Stopher has looked at, seems to be about training with no reference to Customers. Is that the one you want me to comment on?

 

[RainLover]

I took a look at your database. Maybe I do not understand the basis of the model but some fundamental issue I see are:

1. You have set LastName/FirstName as the primary key in tblEmployeeTraining. It is generally advisable that PKs should not carry any real world meaning and names definitely should not be considered as candidate keys. Furthermore, they are not candidates for the primary anyway. It would imply a person can only be associated with an employee-training record once. Surely EmployeeTrainingPK should be the obvious PK in your design, otherwise EmployeeTrainingPK seems redundant.

2. You have not set any constraint on the FKs (EmployeeFK, TrainingTypeFK). So this means a given employee can have a given type of training many times. Is that really the intention? In which case it is hardly relevant to the OP's question.

3. Why is LastName/FirstName in two tables? Are you talking about the same person or not? Data should not be duplicated in different tables (although I don't know if you refer to the same person).

Chris

Thanks Chris for taking the time.

On point Number one re You have set LastName/FirstName as the primary key in tblEmployeeTraining.

You are correct. I don't know how that happened except I may have posted the wrong version. I shall fix it.

On Point Number two re You have not set any constraint on the FKs (EmployeeFK, TrainingTypeFK). So this means a given employee can have a given type of training many times.

This is a Demo Database on Many to Many. I believe it does that which it was intended to do. The setting of constraints is up to the person who is desiging a Database. Unlike your first point this has no effect on the intention. You are however correct in pointing this out. I most likely will adjust this while I am doing the other fix.

On Point Number Three Why is LastName/FirstName in two tables? Are you talking about the same person or not? Data should not be duplicated in different tables (although I don't know if you refer to the same person).

You are correct again and this follows the point you raised in Number One.

It appears clear to me that I have posted an incorrect version.

I thank you for pointing this out and assure you that I shall fix everyting you raised.

 

[stopher]

Noted you have already marked the post solved...

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?

Empty fields do not break normalisation rules. There's really no problem at all in having a field that is only used for a few records. My understanding from what I have read is that while there my be a slight overhead with a reference to a memo field there is no overhead for the space. So, in theory, you could use more space creating a foreign key and a separate table. Note that memo fields are stored differently to normal fields. But even with normal fields the overhead of a blank field is minuscule. I think this is your best solution and solves your problems of your form design.

If you do want a separate table then linking 1:1 using the composite key is really quite easy. First the composite key must be the PK in both tables. Then in the relationship window just drag one of the fields in one table to the corresponding field in the other and the edit relationships window will open. Add the second field of the composite key in the second line for both tables. Tick ref. integrity and click create.

Alternatively you can do it in SQL like this:

ALTER TABLE tblCustEmpNotes
ADD CONSTRAINT cstCustNotes
FOREIGN KEY (EmployeeID,CustomerID)
REFERENCES jctEmployeeCustomer

I prefer composite keys to surrogate because it is a cleaner representation of the model and therefore easier for others to interpret. Nevertheless I have also used Pats solution (post 3) occasionally - as Pat said for convenience.

Chris

 

[RainLover]

Reposted ammended version of Demo Database.

 

[nanscombe]

That's looking better.

What did you mean by?

This appears to go against what you are saying

I see you have a constraint on EmployeeFK & TrainingTypeFK which means you could not enter another record with the same values in those fields.

What if you had a field for the date of the training (Order) and you needed to do a re-sit / refresher for the same person (place another order)? The constraint would stop you from doing this.

I was basing my comments on the OPs orders database where the same company may place multiple orders through the same employee.


I'm off for a kip now, will catch up in the morning. Night, night.

 

[nanscombe]

..If you do want a separate table then linking 1:1 using the composite key is really quite easy. First the composite key must be the PK in both tables. Then in the relationship window just drag one of the fields in one table to the corresponding field in the other and the edit relationships window will open. Add the second field of the composite key in the second line for both tables. Tick ref. integrity and click create.

Alternatively you can do it in SQL like this:

ALTER TABLE tblCustEmpNotes
ADD CONSTRAINT cstCustNotes
FOREIGN KEY (EmployeeID,CustomerID)
REFERENCES jctEmployeeCustomer

I prefer composite keys to surrogate because it is a cleaner representation of the model and therefore easier for others to interpret. Nevertheless I have also used Pats solution (post 3) occasionally - as Pat said for convenience.

Chris

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.

 

[Balthazar]

I'm sorry everyone, but I feel I've slighty misled you. Firstly though, Stopher, thank you for that thorough explanation. It was less about saving space and more about keeping the database looking ‘tidy’. An immature oversight on my part, perhaps.

I feel I've misled you because the Employee/Customer scenario was just an arbitrary exanple. I'm just making a small database to record sales, for my Dad. We have items in the shop and the transactions are unique. Transaction 1 might include Items 1, 2 and 3. If you scanned item 2 again, I just made a VBA script to increment the product sales quantity by 1.


@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

 

[RainLover]

I'm sorry everyone, but I feel I've slighty misled you.

No problem. You wanted to learn something and you did.

 

[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.