CODEWITHSUNDEEP
sqltreeconstraints
Matt B
Matt B

Reputation: 8643

SQL schema for hierachical folder structure

I'm trying to design a SQL schema to represent a folder structure for e-mail, however I'm having some difficulty and while there are a number of other questions on stack overflow regarding SQL schemata to represent hierachical folder/email structures, none seem to answer all the questions I have.

My requirement is as follows:

There are a number of things that I would like to enforce at database level, but have been unable to accomodate them all.

So far I have two tables, a "Folders" table which will contain all folders for all mailboxes and a "SystemFolders" table which contains the definitions of compulsory tables:

CREATE TABLE Folders(
   ID Int
   ,   Mailbox_ID Int
   ,   Name varchar(25)
   ,   Parent_ID Int
   ,   SystemFolder_ID Int
   , PRIMARY KEY (ID)
);

CREATE TABLE SystemFolders(
   ID Int
   ,   Name varchar(25)
   , PRIMARY KEY (ID)
);

My problem now is how to add the constraints.

  1. How can I enforce hierachical integrity (parent or child folders must all belong to same mailbox)?**
  2. How can I ensure that only one system folder of each type can exist within the context of a single mailbox?**

Ideally the solution would not be specific to any particular flavour of SQL. Any help appreciated.

Upvotes: 1

Views: 1835

Answers (1)

dan1111
dan1111

Reputation: 6566

How can I ensure that only one system folder of each type can exist within the context of a single mailbox?

Because you are replicating a file structure, not just the system folders but all folders in a particular level will have to have unique names. You can enforce this by creating a uniqueness constraint. This syntax should work on most systems:

CREATE TABLE Folders(
   ID Int
   ,   Mailbox_ID Int
   ,   Name varchar(25)
   ,   Parent_ID Int
   ,   SystemFolder_ID Int
   , PRIMARY KEY (ID)
   , CONSTRAINT uFolder UNIQUE (Mailbox_ID,Parent_ID,Name)
);

Update: To also require a unique SystemFolder_ID, just add another constraint as above, but on SystemFolder_ID:

  , CONSTRAINT uSysFolder UNIQUE (Mailbox_ID,SystemFolder_ID)

For each mailbox, there can be no duplicate names in a particular folder.

How can I enforce heirachical integrity (parent or child folders must all belong to same mailbox)?

This is more complicated. You might be able to do it with check constraints, though it would be dependent on your implementation (and it might not be a very good idea). Another, probably better, option would be creating a trigger for this. In either case, you would have to verify both the parent of a particular row and all children of a particular row. If you don't do both, then later modifications could indirectly corrupt things.

Upvotes: 3

Related Questions