It's common to not allow records to be deleted from your database at all in circumstances like yours.
If someone deleted a user group because they wanted to change something as simple as the name, you would be required to propagate any changes to underlying records that rely on that key - a cascading update. That's not bad in some situations, but causes problems in others - for instance, if someone has a hard copy report, it's not going to be one-to-one with the database anymore because someone changed past records. A cascading delete is far worse. It means that some perfectly valid data from last month is now gone. So in this kind of scenario it's better to mark a record as being inactive. In your situation, having a many-to-many table linking GroupType and/or GroupStatus (with System etc...) to UserGroup primary key could do it, or just another two columns to mark a record as System true/false and Active true/false would be just as useful. Then you can use a view to show only active records, if you want to save time when performing SQL operations on the database, preventing having to perform a filter every time. Perform your select, updates, etc ... using the view instead of the underlying table. Cheers, Richard A. Hein Software Developer Level Platforms Inc. 36 Antares Drive, Suite 200, Ottawa, Ontario * 613-232-0098 * [EMAIL PROTECTED] Please visit us at http://www.levelplatforms.com -----Original Message----- From: Stefan Holdermans [mailto:[EMAIL PROTECTED]] Sent: Saturday, July 13, 2002 7:30 AM To: [EMAIL PROTECTED] Subject: [ADVANCED-DOTNET] Business Entity Layer and Database Design. Currently I'm working on a enterprise app and I've some questions regarding the design of the Business Entity Layer and the underlying database. I'm modeling a domain model in a Business Entity Layer. In my domain model I've users. Each user is contained in one or more user groups. For now, there are just two user groups: junior and senior users. A straight forward approach would be implementing a User class and a UserGroup enumeration. Actually, a user can be contained in both user groups, so this enumeration would be decorated with the Flags attribute. In the database I then would have a User table and a UserGroupContainment table that has two columns: UserId and UserGroup. But I know, somewhere in the future, my app will have to cope with user-group management, i.e. administrators can add and remove custom user groups. So I'll probably will have to introduce a UserGroup class in my Entity Layer and a UserGroup table in my database. The UserGroup enumeration will be removed than and a User object will get associated with one or more UserGroup objects. The UserGroup column in the UserGroupContainment table will become a foreign key to the UserGroup table. But --- and this what I'm struggling with --- the junior-users and senior-users group will need a special treatment: they're system group and the app should not allow them to be removed. How will I implement that? Should I hardcode these restrictions in the Entity Layer. Should there be an extra column in the UserGroup table that indicates whether a group is a system group? I guess this is an issue that arises quite often, so I'm very interested in hearing how others solve this problem. I really haven't decided which way to go with the implementation of my app, so I'm open to any suggestion... -- Stefan You can read messages from the Advanced DOTNET archive, unsubscribe from Advanced DOTNET, or subscribe to other DevelopMentor lists at http://discuss.develop.com. You can read messages from the Advanced DOTNET archive, unsubscribe from Advanced DOTNET, or subscribe to other DevelopMentor lists at http://discuss.develop.com.
