In Security Module why tables Relation create problem ??
-
Employee: (employeeId , branchId ,employeeName,departmentId, userId ,address1,address2,city,state,zip,country,mobile,phone,hireDate,active)
User: (userId,branchId,username,userPassword,description,alertLoginStatus,groupId)
UsersGroups: (userId,branched,groupId)
GroupRights
groupId,admObject,croObject,dealerObject,rights)Groups: (groupId,groupDescription,groupName)
AdmObject: (admObject,objectName)
CroObject: (croObject,objectName)
DealerObject: (dealerObject,dealerId,ObjectName,commissionCode)
Explanation:
This is our security module First of all User table have userId,BranchId as a primary key .user table primary key relate to the employee table ,Users Group as a foreign keys. In the Groups table groupId is a primary key and relate to the UsersGroups,GroupsRights table as a foreign key. AdmObject, CroObject, DealerObject tables have primary keys and relate to the Groups Rights as a Foreign Key.
When we start /run as a visual studio .Net this application they create a problem and send the error that FK_ User and Groups ,FK_Employee , Users that’s relation are not correct So what we do to solve this problem .??
-
I've never used Visual Studio.NET, but if you post the exact error message you get, somebody may be able to help. In the meantime, could it be that the existing data in the tables violates the foreign key constraints you are trying to create?
John
-
Here is some short hand for what you have provided, PK = Primary Key Constraint and FK = Foriegn Key constraint:
-- PK groupId
Groups
-- PK userId,BranchId
-- FK to Groups groupId possible
User ON Groups.groupId = User.groupId
-- PK userId,branchId,groupId is not possible due to missing branchId
-- FK to Groups groupId possible
UsersGroups ON Groups.groupId = Groups.groupId
-- PK userId,BranchId
User
-- PK employeeId,branchId
-- FK to User userId,BranchId possible
Employee ON User.userId = Employee.userId
AND User.branchId = Employee.branchId
-- UsersGroups.userId may be duplicated for each User.branchId
-- PK userId,branchId,groupId is not possible due to missing branchId
-- FK to User userId,BranchId not possible missing branchId
UsersGroups ON User.userId = UsersGroups.userId
-- Probably is not desired due to duplication
AND User.groupId = Groups.groupId
-- PK groupId
-- FK to UsersGroups is not possible missing PK
Groups ON UsersGroups.groupId = Groups.groupId
-- PK groupId
Groups
-- PK groupId,admObject,croObject,dealerObject possible if all are NOT NULL
-- FK to Groups possible
GroupRights ON Groups.groupId = GroupRights.groupId
-- PK admObject
AdmObject
-- PK groupId,admObject,croObject,dealerObject possible if all are NOT NULL
-- FK to Groups possible
GroupRights ON AdmObject.admObject = GroupRights.admObject
-- PK croObject
CroObject
-- PK groupId,admObject,croObject,dealerObject possible if all are NOT NULL
-- FK to CroObject possible
GroupRights ON CroObject.croObject = GroupRights.croObject
-- PK dealerObject
DealerObject
-- PK groupId,admObject,croObject,dealerObject possible if all are NOT NULL
-- FK to DealerObject possible
GroupRights ON DealerObject.dealerObject = GroupRights.dealerObject
The problems I see are:
User -> UsersGroups cannot have a FK defined due to missing branchId portion of User PK
User -> Groups using UsersGroups.groupId or User.groupId is possible, is this desired?
All tables must have a PK, otherwise you risk not being able to UPDATE or DELETE a row
All tables should have a FK defined when related, otherwise you will spend loads of time cleaning up orphans
The goal of a good design is to accommodate the requirements, and keep the users from doing the wrong thing.
Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply