Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 DB design help/assistance question
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

1 Posts

Posted - 04/24/2008 :  10:48:12  Show Profile  Reply with Quote
I'm hoping to get some feedback from people with greater expertise then mine on how to accomplish this with a database schema design.

I'm tasked at putting together a simple database where the core entity would be a "Document". One of the main attributes would be the "path" to that document. Soentries might look like this:


What the database needs to do essentially, is maintain a document "classification scheme" for each stored organization - in the example above, OrgX has a simple "big Bucket" where all their documents go, OrgY has a much more granular classification scheme where each document exists in some "leaf" node, and OrgZ is somewhere in the middle.

The only thing that I can be guaranteed is that each organization will have their own "directory structure" where documents are stored. The records will number in the millions, and each organization will probably contain hundreds of thousands of records.

What is the best way to create a database where a user could efficiently write a query like:

select *
From theTable(s)
Where Department = 'Electronics' and SubDepartment = 'Lightning' and OrgName = 'OrgY'

I'm hestitant to go the XML route because I'm afraid the performance would be awful if the optimizer was forced to do a tablescan on all values for all queries. What kind of structure would you recommend in order to provide better querying performance, while also taking into account that the next organization added to the database my have a "directory structure" like:

Thanks in advance for any tips!

Patron Saint of Lost Yaks

30421 Posts

Posted - 04/24/2008 :  11:21:28  Show Profile  Visit SwePeso's Homepage  Reply with Quote
If you make sure all DocLocationClassifiers ends with a "/", try this
FROM	theTable
WHERE	DocLocationClassifier LIKE '/' + @OrgName + '/' + @Department + '/' + @SubDepartment + '/%'

E 12°55'05.25"
N 56°04'39.16"

Edited by - SwePeso on 04/24/2008 11:33:39
Go to Top of Page
Insecure what ??

38 Posts

Posted - 04/28/2008 :  21:42:40  Show Profile  Visit's Homepage  Reply with Quote
I'll start you off...

Company Table
id {OrgX, OrgY, OrgZ}

Department Table
name {Electronics}

SubDepartment Table
name {Lighting}

Section Table
name {Fixtures}

Document Table
directory_node {company, department, subdepartment, section}
directory_id {company_id, department_id, subdepartment_id, section_id}

you need to decide whether a doc can be owned by more than 1 company, whether a directory can be owned by more than 1 company, etc.

you probably also want a function that returns the string directory name for a given section, subdepartment, department, or company id.

your specs weren't quite precise enough, but if you look at this design you'll see flexibility.

depending on the data, you may decide to denormalize the company, department, subdepartment, section tables.

Edited by - on 04/28/2008 21:44:37
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000