Circular Reference table design

Question

Circular Reference table design

BlackCat

SSC Veteran

Points: 231
More actions
October 16, 2010 at 2:18 pm

#230532

I have read a couple of article in relation to circular reference in db and it seems there really isn't one answer. I have tables
Businesses, BusinessMember, BusinesGroups, BusinessGroupMembers and they are related as follows
Business
bus_pk
BusinessMember (Rules: businessmembers belongs to one and only one businesss)
busmember_pk
bus_fk
BusinessGroup (Rules: businessgroup belongs to one and only one businesss)
bussgroup_pk
bus_fk
BusinessGroupMember (Rules: businessmembers can belong to multiple businessgroup within a business)
bussgroupmember_pk
bus_fk
busmember_fk
What I will like to know is if this design is a circular reference design.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

It depends if what you have listed as the "_pk" columns. If these single columns, then yes, this is a circular reference and can be a problem since declarative referential integrity rules cannot support the constraint that "for a business group member, the member business and the businessgroup business must be the same business for all business group members"

The solution is that instead of independent primary keys, define dependent primary keys where the primary keys of the dependent tables include the primary key of the parent. When there are multiple parents, such as BusinessGroupMember, then a check constraint is used to enforce the business rule.

Create table Business

( BusinessId integer identity(1,1) not null

, constraint Business_PK primary key (BusinessId )

);

Create table BusinessMember

( BusinessId integer not null

, BusinessMemberId integer identity(1,1) not null

-- note the multi-column primary key

, constraint BusinessMember_PK primary key (BusinessId, BusinessMemberId )

, constraint Business_FK_BusinessMember foreign key (BusinessId ) references Business (BusinessId )

);

Create table BusinessGroup

( BusinessId integer not null

, BusinessGroupId integer identity(1,1) not null

-- note the multi-column primary key

, constraint BusinessGroup_PK primary key (BusinessId, BusinessGroupId )

, constraint Business_FK_BusinessGroup foreign key

(BusinessId ) references Business (BusinessId )

);

Create table BusinessGroupMember

( BusinessId_BusinessMember integer not null

, BusinessMemberId integer not null

, BusinessId_BusinessGroupinteger not null

, BusinessGroupIdinteger not null

-- note the multi-column primary key

, constraint BusinessGroupMember_PK primary key

( BusinessId_BusinessMember

, BusinessMemberId

, BusinessId_BusinessGroup

, BusinessGroupId

)

-- Business of the member must be same as the Businss of the Group

-- If the rules changes, then drop the constraint

, constraint BusinessGroupMember_CK_Business_Same CHECK as

( BusinessId_BusinessMember = BusinessId_BusinessGroup )

, constraint BusinessMember_FK_BusinessGroupMember foreign key

(BusinessId_BusinessMember, BusinessMemberId)

references BusinessMember

(BusinessId , BusinessMemberId)

, constraint BusinessGroup_FK_BusinessGroupMember foreign key

BusinessId_BusinessGroup , BusinessGroupId )

references BusinessGroup

(BusinessId , BusinessGroupId

);

With this model, the same Business row is returned regardless of path:

BusinessGroupMember ==> BusinessGroup ==> Business

BusinessGroupMember ==> BusinessMember==> Business

SQL = Scarcely Qualifies as a Language

BlackCat SSC Veteran Points: 231 More actions · Answer 2

Brillant!! In providing the answer to my problem, you also answered my general question of what a circular reference really is. Thanks you very much. I really appreciate it.

BlackCat SSC Veteran Points: 231 More actions · Answer 3

Carl, have a quick question. Instead of having BusinessId_BusinessMember and BusinessId_BusinessGroup in the BusinessGroupMember table, couldn't I just have BusinessId in BusinessGroupMember table and use that column as part of the foreign key to both BusinessGroup and BusinessMember.

Carl Federl One Orange Chip Points: 25384 More actions · Answer 4

Instead of having BusinessId_BusinessMember and BusinessId_BusinessGroup in the BusinessGroupMember table, couldn't I just have BusinessId in BusinessGroupMember table and use that column as part of the foreign key to both BusinessGroup and BusinessMember.

Yes but then you are hard coding a business rule into the logical data model.

Imagine that the requirements change and the model must now also support "Trade Associations" where the BusinessGroupMembers will no longer obey the rule ""businessmembers can belong to multiple businessgroup within a business" but is instead changed to

When the BusinessGroup is for a Business , the business of the group must be the same as the business of the member.

When the BusinessGroups is for a Trade Association, the business of the group must be different than the business of the member.

If the model has both BusinessId_BusinessMember and BusinessId_BusinessGroup, just drop the constraint and then add a trigger to check the business rule.

If the model has only one column, then there is a significant model change plus all SQL on the BusinessGroupMember will need to be reviewed and modified appropriately.

SQL = Scarcely Qualifies as a Language