databse froeign keys 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

[KevinM1]

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

 

Why would an item in the submenu table even be in the main menu table?

[freakstyle]

what is the difference between a menu and a submenu, in terms of the data required to be saved?

 

Generally speaking you should just have one table menu, and account for relationships in the that one table.

So you could have 'parent_id', and if the menu is a 'submenu' it's parent_id would be the 'id' of the menu item that it relates to.

 

If you delete a child you don't need to delete the parent, the children are independently saved and removed from their parents.

 

For menus, I find using a tree structure to be the most effective in my processes and the life cycle of the applications.

 

Good luck.