databse design help

[jake2891]

Im just curious as what the best approach to take in designing a database is with foreign keys. If i have a menu table and a submenu table and a submenu belongs to a menu then i presume submenu would reference the menu table. But what happens when i want to delete an item in the submenu table? i would have to delete from the menu table first? is it best to not decalre a relationship at all? or to just not allow deletion? also when is it best to use foreign keys when there will be trouble deleting records?

 

thanks

[JonnoTheDev]

MySQL has features when it comes to linked tables. For instance in your case if you delete a menu then all linked sub-menus will also be deleted. You can read up on this.

However if you database is simple you can simply run 2 queries. Delete the single record from the parent table, then delete records from the linked table using the parent tables' primary key i.e

 

DELETE FROM menus WHERE menuId=1

DELETE FROM sub_menus WHERE menuId=1 (menuId is the foreign key)

 

If you are deleting a record in the sub_menu table nothing needs to happen in the primary table because it is a one to many join. i.e. 1 menu can have many sub menus.

 

You should never leave records in a table that are disjointed as your tables will end up cluttered with rubbish and it could cause issue.

[jake2891]

but if im deleting from the submenu table and it comes to the last record in the table it wont allow me to delete this because of the relationship. so is it best to leave out the relationship beause i want to be able to delete all records in the submenu table without having to delte its corresponding menu? thanks

[JonnoTheDev]

but if im deleting from the submenu table and it comes to the last record in the table it wont allow me to delete this because of the relationship. so is it best to leave out the relationship beause i want to be able to delete all records in the submenu table without having to delte its corresponding menu? thanks

Then you must already be working with linked table features. Think about the logic. If it is the last item, then the menu cannot exist as it requires sub menu records. remove the linked table features you have in place. You still require the foreign key relationship though. I would use simple queries to remove records as stated in my prev post.