SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

ElianaS
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:

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!

SwePeso
Patron Saint of Lost Yaks

Sweden
30217 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
SELECT	ID,
	DocName,
	DocLocationClassifier
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

m_k_s@hotmail.com
Insecure what ??

38 Posts

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

Company Table
id {OrgX, OrgY, OrgZ}
...

Department Table
id
company_id
name {Electronics}

SubDepartment Table
id
department_id
name {Lighting}

Section Table
id
subdepartment_id
name {Fixtures}

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

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 - m_k_s@hotmail.com on 04/28/2008 21:44:37
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.08 seconds. Powered By: Snitz Forums 2000