ah I had a little look and I understand i need a composite key however I am not allowed to mix it up like that I have to follow the guides they give us and it has got to be 4 tables which is what I have got however I think I went wrong somewhere in normalisation
I think in Thom's example he didn't write out the Client table because it's fine as is. I also think he has better table names, and that helps to think about the relationships between items better. The table you call Price is what he calls Equipment, and the table you call Equipment is what he calls Equipment Order.
It's probably easiest to think of it in terms of 3 entities, Client, Order, Equipment. I assume the following relationships exist:
- A client can have 0 or more orders, and each order only belongs to 1 client
- An order can have 1 or more equipments on it, and each equipment can be on 0 or more orders
- Since order and equipment have a many-to-many relationship, there needs to be an intersection table between them
Right I had a look and realised my relationships were wrong between the original order and equipment entities. I have taken on board the advice and made a new ERD for an example showing what I believe is the correct relationship.
Because the 'order table' can have multiple 'equipment orders' I have put 1 to many, as for the other way around the 'equipment order' can only be in 1 and only 1 order (I think).
The equipment and price attributes itself reside inside the 'equipment' entity... Unfortunately this is a very crude assignment as he has worded it really bad in the original question to catch us out. They have already had to make changes to names multiple times because of the influx of confusion with the other students.
