DB design help/assistance question

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

All Forums

General SQL Server Forums

Database Design and Application Architecture

DB design help/assistance question

Author

Topic

ElianaS
Starting Member

1 Post

Posted - 2008-04-24 : 10:48:12

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

30421 Posts

Posted - 2008-04-24 : 11:21:28

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"

m_k_s@hotmail.com
Insecure what ??

38 Posts

Posted - 2008-04-28 : 21:42:40

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources