Junction table limitations

[Homegrownandy]

This is for the parts database I'm constructing. Bit of a general question about how I'm using a junction table.



This table is in-between part and suppliers. It will show the ID of both tables and then, the suppliers best price and the reference number.

Each part may only have 1 entry from each supplier. So I believe the solution would be to have the primary key consist of the 2 foreign keys on the table, ensuring they are unique.

I can probably find that solution on google. But is that what's recommended?

[aytee111]

Yes.

But why is this a table? It sounds like it should be a query, it changes all the time.

[pbaldy]

But why is this a table? It sounds like it should be a query, it changes all the time.

If it was a query, where would it get the price?

[aytee111]

I took it from here:

It will show the ID of both tables and then, the suppliers best price and the reference number

[pbaldy]

Sounded to me like a classic many-to-many junction table. The supplier and part ID's, then the supplier's price for that part, which wouldn't (couldn't) be in either of the other tables.

[aytee111]

My point is that it the data changes all the time, it will have to be continuously deleted and added. To me that is a lot of maintenance that should be avoided, too much room for errors to happen. Whereas a query would not need to be touched once created and would produce the same results.

Addendum - I was assuming that the price was in a third table!

[pbaldy]

My assumption was that this is that third table.

[Homegrownandy]

Yes its just a junction table. Also, we have no control over a price if a supplier changes it, we have to change it in the database. So yes this is the third table.

To clarify.

Suppliers have their own table and this never changes.
Parts have their own table and this never changes.

The junction table would link all suppliers for a particular part and we would request their best price. I wont be deleting anything ill use an update query after an import of information we get from our suppliers.

The idea is, when we create a quote, this will be sent to the suppliers for up to date price and this will then be updated and the cheapest is selected. (could be based on date criteria, so it only requests old data).


Anyways, most of the time, I'm able to find the answer myself but I find it helpful to ask about my problem here. A lot of times people have given me a different approach or something to think about.

Thanks

[aytee111]

I would seriously consider changing your thinking. If Joe from Acme Brush Co. gives you a price today, say 10.00, and you update the table, you will never know that yesterday his secretary gave your secretary a price of 5.00. The beauty of a database is the ability to look around and make decisions based on good data. I would have a table that said: on this date/time this person from this supplier quoted a price of this. Otherwise you have no comeback, no records, no audit trail.

And should quantity come into it - if you buy 1 or if you buy 10,000, does the price change?

[Homegrownandy]

Thanks for the feedback, Ill add an append query for any that will be updated. This will pull the old data into a new table for price history. So every time a new price is added we store the old data as reference, I could flag up price changes ect. Good idea.
Its not essential, we're creating a system where the suppliers compete for business, whatever price they quote is what we will go by (we do get quite big discounts at the moment). Having said that, knowledge is power, and I agree with what you're saying. Its also easy enough to add to what I'm doing.


The quantity discount is also a good point. But to be honest that's usually reviewed annually and applied to all orders with our suppliers. Even if I order 1 item tomorrow but over the year I order 10000 I will get the same discount.

Ill have a think about that though. I wont be doing that short term, but once everything else is running I could look into that.

Let me know your thoughts, Thanks.