MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:June 9 2007 3:06pm
Subject:Re: Arbitrary Boolean Functions as Relational Database Structure?
View as plain text  
Hi David,

David T. Ashley wrote:
> Hi,
> I'm implementing a software release database.  Users may aribtrarily be
> members of groups (a many-to-many mapping), and each software release may
> contain multiple files.
> I'd like to allow users the maximum flexibility in deciding who may view
> what software releases.  The most obvious approach is to allow 
> specification
> in the form of "Release X may be viewed by Users in Group Y or Group Z", 
> per
> release.
> In this case, the database design would be something like:
> [Users] (many:many) [Groups] (many:many) [SwReleases] (1:many) [Files]
> The many:many relationship between groups and software releases specifies a
> Boolean function, of the form "is in Group X or is in Group Y or ...".
> Since one knows the user who is logged in (for a web database), one can do
> an outer join and quickly find all the software releases that the user may
> view.  I believe one can do it in O(log N) time.
> However, the Boolean function is of a fairly special form ("is in Group 
> X or
> is in Group Y ...").  This is the only form where it seems to translate to
> an SQL query naturally.
> Here is my question:
> Is there any interesting way to structure a database so that other forms of
> permissions can be specified and translate directly into SQL queries?
> For example, what if, for a software release, one says, "to view this
> software release, a user must be in Group X or Group Y, but not in Group Z
> and not user Q"?  Is there a database structure and a corresponding 
> O(log N)
> query that will quickly find for a given user what software releases may be
> viewed?

I have developed such a system over the past 7 years or so.  It is quite complex 
to explain, but it's really simple when you get down to it.  I wrote a two-part 
series about it on my blog:

 From your description of the problem, I would say a subset of my solution could 
fit your needs exactly, and be about as simple and efficient as I believe is 

Arbitrary Boolean Functions as Relational Database Structure?David T. Ashley9 Jun
  • Re: Arbitrary Boolean Functions as Relational Database Structure?Baron Schwartz9 Jun
  • Re: Arbitrary Boolean Functions as Relational Database Structure?Chris W10 Jun