Database Design Question - General SQL Database Question & Answer

Member of the LearnItFirst.com Video Training Network	\| LearnSqlServer.com \| SQL SSIS Training \| SQL Programming Tutorials \|

Welcome Guest

Search | New Posts | Members | Log In | Register

LearnSqlServer.com's SQL Server Forums » SQL Server Database Administration » General SQL Database Question & Answer » Database Design Question

Database Design Question

Options

Previous Topic · Next Topic

ElianaS

Posted: Thursday, April 24, 2008 9:45:37 AM

Rank: Newbie

Joined: 4/24/2008
Posts: 1
Points: 3

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:

ID.....DocName..............DocLocationClassifier
1.......First.doc................../OrgX/
2.......Second.doc............./OrgY/Department/SubDepartment/Section
3.......Third.doc................/OrgZ/Department

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:
/NewOrg/Department/SubDepartment/SubSubDepartment/Section

Thanks in advance for any tips!

Scott Whigham

Posted: Tuesday, April 29, 2008 7:23:14 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

Hi there -

The first thing that comes to mind is that you should take a look at the SQL Server Reporting Services tables in the ReportServer database. They have similar designs to what you mention and you might get some quality ideas there. I understand the jump to XML since, logically, it would easy to write the queries but you're right about performance being slower over millions of records. You can create xml indexes to help you in that regard however. Personally I would favor the XML approach to something like this. You could create an xml data type column, create a primary index and, if necessary, secondary indexes. The challenge here is (1) space since these indexes are going to be large, and (2) modification slow-down since the xml data has to be totally shredded upon insert or update. I think you'll wind up finding that a primary index and a PATH secondary XML index will solve your problem best. You'll want the path secondary index since you'll be using path expressions frequently (along with .exists()).

Bob Beauchamin has a nice whitepaper on using xml indexes: http://technet.microsoft.com/en-us/library/ms345121.aspx
Another is the XML Best Practices for SQL Server 2005: http://msdn2.microsoft.com/en-us/library/ms345115.aspx

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.