Many-to-many relationship or not?

Question

Hey guys 8D

I'm in a bit of trouble trying to define the type of a relationship between 3 tables. They are PRODUCTS, SUPPLIERS, and the third, QUANTITY, where I have a combination of product and supplier and a field storing the quantity of products of that combination.

I can have one product from one or more suppliers, that's why the table QUANTITY exists. Eg.: I can buy the same notebook A from the supplier X and supplier Y.

The table QUANTITY have the fields prod_id, supp_id and qty. Both PRODUCTS and SUPPLIERS have an ID field. I can't figure it out, so I'm asking for a little help from smarter guys than me, meaning you all. :D

Thanks a lot.

[EDIT]

I'm very sorry, I should've been more clear from the start. This is about the modelling. :D

Answer 1

You appear to be puzzled by a relationship between entities having additional attributes. I agree that it is fairly uncommon, but there's nothing invalid about it and you appear to be modeling it correctly.

Answer 2

The situation described in your posting seems to answer whatever question you are asking. That is, you state a correct solution to your problem in the question.

Table PRODUCTS will have field id (I prefer to call it prop_id, but that's just me). SUPPLIERS will have id (or, in my world, supp_id). Table QUANTITY will have (prop_id, supp_id, and quantity).

Ideally, prop_id in PRODUCTS will be protected as primary key or a unique index. The same with supp_id in SUPPLIERS. In QUANTITY, you will create a primary key or unique index on the combination of prop_id and supp_id (since each combination should occur only once, even though each column considered alone might have duplicates).

Finally, I generally prefer to have a single-column integer primary key on every table, so (if it were me, others disagree) I would make (prop_id, supp_id) a unique index and create a new primary key column quantity_id in the QUANTITY table.

Answer 3

Since you ask about relationships, here's a little more information which may help. A many-to-many table exists at the level of design (concept), but is built in the db by building two one-to-many relationships.

Although you don't have to, you probably should declare that quantity.supp_id is a foreign key from suppliers, establishing a one-to-many relationship from suppliers to quantity; and declare that products.prod_id is a foreign key from products, establishing a one-to-many relationship from products to quantity. Doing so may help the db to optimize queries. It will also enable you to set up cascading deletes so that deleting a supplier from the suppliers table will automatically also delete all of that supplier's records in the quantity table (if you want that to happen).