Search code examples
phpmysqlrelationships

MYSQL modeling relationships: Design and UPDATE/DELETE

I am trying to understand relationships fully, and have gone through alot of tutorials. Still i linger on a few things (using MySQLWORKBENCH): enter image description here 1. Every user can upload a product 2. Every product can have multiple categories, bids, ratings, etc 3. Every user can have multiple phonenumbers (there are more, but this is the basic setup)

Is this correct?: 1 - I used a 1:n relationship, since every user can upload multiple products. 2 and 3. I used n:m relationship, since there can be multiple products with multiple categories, bids, ratings, etc.

DELETE/UPDATE: I used ON UPDATE CASCADE and ON DELETE CASCADE everywhere where there is a foreign key...that being 'product', 'category_tags_has_products', 'bid_price_has_product', 'phone_has_user'.

I tried to delete a product like this (php): mysql_query("DELETE FROM product WHERE id='$id'"); I get a 1054 error, which is a foreign key error. What are the best practises here? It is to my understanding that i shouldn't need to do deletions in any other than the parent-table?

Thanx!

Solution

  • You have a lot of identifying relationships, which mean that the foreign key form part of the primary key on the second table. This is not necessary in most instances, and is only really useful in instances such as link tables.

    To this end I would change the user->product link to be non-identifying, which will make user_id a Foreign Key instead of being part of the Primary Key. In workbench the shortcut for a 1:n non-identifying relationship is key '2' (where as '4' is identifying). This in turn should remove the user_id fields from the tables which product links onto.

    When you delete a product, it should cascade to the 3 link tables that it links to. At present it may be that it is trying to delete from users also depending on how the FK is set up. The following should cascade deletions (assuming a deletion is permanent and you just want to clear out all linked records)

    DELETE FROM product -> deletes from any table with product_id in DELETE FROM user -> deletes from any table with user_id in

    The same applies for phone, rating, bid_price, category_tags.

    I hope this if of use, if you need any further pointers feel free to shout :)