Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!prodigy.com!news-FFM2.ecrc.net!news.tele.dk!news.tele.dk!small.news.tele.dk!newsgate.cistron.nl!news.cistron.nl!newsbin.cistron.nl!not-for-mail From: Jared of Europa Newsgroups: comp.databases.oracle.server Subject: Re: Fine-grained Access Control and constraint violations Date: Fri, 08 Aug 2003 22:10:55 +0200 Organization: Cistron Netherlands Lines: 52 Message-ID: References: <945a3a61.0308080346.67708a83@posting.google.com> NNTP-Posting-Host: cust.93.22.adsl.cistron.nl Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Trace: enterprise.cistron.net 1060373454 98278 195.64.93.22 (8 Aug 2003 20:10:54 GMT) X-Complaints-To: abuse@cistron.nl NNTP-Posting-Date: Fri, 8 Aug 2003 20:10:54 +0000 (UTC) X-Newsreader: Forte Agent 1.92/32.572 Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:240124 In this foul year of our lord Fri, 08 Aug 2003 22:36:17 +1000, quarkman proclaimed: >Not sure why you'd think using a unique constraint is 'clunky'. Not the constraint per sé is clunky, but having to attempt to insert a name (along with valid values for other constrained fields and foreign keys) to see if the name is unique. In some cases we'd want to verify uniqueness of the name without actually doing the insert. >What's clunky is the way you've designed this. If uniqueness of names is >that important to you, create a sequence, and append the sequence to >whatever name the user enters, regardless of whether it's the first >instance of that name or not. Sure... but users will not accept that they are assigned a name that they did not enter. If they enter 'test', they expect to see 'test', not 'test23'. Only when a user copies a record or creates one using a name that is in use should he get 'test (2)'. They understand and accept that (especially since that's how it currently works) >Either that, or think things through: what's the primary key on this table >(I'd have thought it was the ID column)? If that's the primary key, then >what does it matter if a name duplicates? And if it *does* matter that the >name duplicates, then ID is not the primary key (or shouldn't be), but name >itself should be (at least part of) the primary key. The ID column is the primary key and is what other tables use as FK. The constraint on the name is something that is subject to change in the future... for example, enforce a unique name only per department, per user group, or even allow duplicates. It is not a suitable primary key. Duplicate names can be confusing: users could inadvertedly open the wrong case in the application. You can be sure that many users will create a case called 'test'.... all in different departments, so they will not see each other's cases. But there are users who can see cases from multiple departments, and they will get confused. And even within one department this can happen. (Security is per user, not per department). >Fix the design, not hunt around for workarounds that really will be clunky. No can do... I'm supposed to just add the security to an existing design. A redesign would be nice but out of the question. Ronald In 't Velt Jared of Europa -*- "I have often deprived myself of the necessities of life, but I have never consented to give up a luxury."