Hi,
How can I get rid of a circular relationship in my db structure. I have an entity called Item. An item can have a sub item/s (circular relationship). An item can have more than one rate depending on what financial year it is(rate_per_year entity created for that purpose and a 1-m relationship). If an item has a sub item, then it may have no rates(I need this relationship broken if a sub item is created). Any help will be greatly appreciated.
Thanx in advance
If the item->subitem chain can continue at multiple levels, then you have no real choice but it like you have it. If only a top-level item can have subitems, then you can break out the structure into two tiers, possibly item and group where only item can have a rate, and may or may not have a group.
What you have described is the Parts Explosion Problem. When I was at university, this was something that could not be solved by relational databases, and was one of the main selling points of Object Oriented Databases. It appears that things may have moved on now, depending on which DB you are using.
This page looks interesting Sql Anywhere Users Guide - Parts explosion problems, but unfortunately you didn't mention which RDBMS you were using. It also appears to be supported by SQL Server 2005, so perhaps you will be in luck. Try searching for "Recursive SQL queries" and whichever DB you are using.
Best of luck