Design Problem...Please Help!!

Hi,

I'm designing a simple database for filing system:

There are two levels of files (both look_up tables):
tlkpFile1, tlkpSubFile1 and a transaction table, tblFilings, for
filings (when documents ready for filings, user just pick the file name
from either look-up tables and insert to this table). tlkpSubFile1
includes files that are sub files from the files in tlkpFile1.


The design I have come-up with is like this:

tlkpFile1: tlkpSubFile1:
ID(PK) Name_File1 ID (PK) Name(FK_File1) Name_File2

1 Departmetns 1 Departments Marketing
2 Sales 2 Departments IT
3 Sales Jan
3 Sales Feb

My question is how should I design the tblFilings (transaction table)
with respect to those two look-up tables?? I'm thinking to have a
field, FiledIn, which will have a many-many relationship with both
fields in the look-up tables (Name_File1 and Name_File2)

tblFilings:
ID(PK) FiledIn

1 Sales **this from tlkpFile1
2 IT **this from tlkpSubFile1


MTIA,
Grawsha

Re: Design Problem...Please Help!!

If there are only two levels of parent and sub file then it looks like
you could do it with something like the following. This is based mainly
on my own assumptions. You are in a much better position than I to
understand your requirements.

CREATE TABLE Files (file_id INTEGER PRIMARY KEY, file_name VARCHAR(20)
NOT NULL UNIQUE, parent_file_id INTEGER NULL REFERENCES Files
(file_id))

CREATE TABLE Filing (file_id INTEGER REFERENCES Files (file_id))

Forget the concept of "lookup tables". Good database designers don't
recognize such a thing. All entities are treated as equal in the
relational model and there is only one type of table. Also, practically
everyone hates "tbl" prefixes on tables. They only make the name harder
to read and everyone will think you are an Access programmer ;-).

--
David Portas
SQL Server MVP
--

Re: Design Problem...Please Help!!

Thanks david,

This is what I want. Now, If want to add one more sub_sub level table
(its one-many relationship to tlkpSubFile1), do I need to modify the
design? I did test the table (the new one )and found it ok, unless I'm
missing something. Please help.


MTIA,
Grawsha

Re: Design Problem...Please Help!!

You should be OK with the design of the Files table to represent the
hierarchy as long as the maximum number of hierarchy levels is know.
For a hierarchy of unknown maximum depth it doesn't work so well
because there isn't a set-based way to relate all levels of the tree in
a single query.

--
David Portas
SQL Server MVP
--

Re: Design Problem...Please Help!!

>> For a hierarchy of unknown maximum depth it doesn't work so well
because there isn't a set-based way to relate all levels of the tree in
a single query. <<

That is what a nested set model does. The levels are determined by
(rgt-lft), with a larger difference meaning a higher level and 1 being
a leaf node.

Re: Design Problem...Please Help!!

"--CELKO--" wrote in message
news:1113744841.378154.150910@z14g2000cwz.googlegroups.com.. .
> >> For a hierarchy of unknown maximum depth it doesn't work so well
> because there isn't a set-based way to relate all levels of the tree in
> a single query. <<
>
> That is what a nested set model does. The levels are determined by
> (rgt-lft), with a larger difference meaning a higher level and 1 being
> a leaf node.

True, or soon through recursion via a recursive WITH, a set-based,
declarative, Standard-compliant (since SQL:1999), and portable
(already provided by DB2 and Oracle) form which we'll finally (!)
see in SQL Server 2005. This is not to say that I wouldn't prefer
a nested-set-model approach still in many cases, I would, but the
possibilities with a recursive WITH are interesting for those wanting
to both keep it relationally pure and computationally more complete.
And, yes, I do mean in addition to tree algorithms.

--
JAG

Re: Design Problem...Please Help!!

>> This is not to say that I wouldn't prefer a nested-set-model
approach still in many cases, I would, but the
possibilities with a recursive WITH are interesting for those wanting
to both keep it relationally pure and computationally more complete.
And, yes, I do mean in addition to tree algorithms. <<

I don't like CTE for trees because it is a loop that runs under the
covers and gets expensive when you do a WITH RECURSION. But to use it
for creating a VIEW on the fly to avoid repeating a derived table over
and over is really handy:

WITH (<> ) AS X (..)
SELECT X1.a, X2.a, ...
FROM X AS X1, X AS X2
WHERE X1.foo_date = X2.foo_date - INTERVAL '1' DAY;

Re: Design Problem...Please Help!!

"--CELKO--" wrote in message
news:1113751194.601691.268940@o13g2000cwo.googlegroups.com.. .
> >> This is not to say that I wouldn't prefer a nested-set-model
> approach still in many cases, I would, but the
> possibilities with a recursive WITH are interesting for those wanting
> to both keep it relationally pure and computationally more complete.
> And, yes, I do mean in addition to tree algorithms. <<
>
> I don't like CTE for trees because it is a loop that runs under the
> covers and gets expensive when you do a WITH RECURSION. But to use it
> for creating a VIEW on the fly to avoid repeating a derived table over
> and over is really handy:
>
> WITH (<> ) AS X (..)
> SELECT X1.a, X2.a, ...
> FROM X AS X1, X AS X2
> WHERE X1.foo_date = X2.foo_date - INTERVAL '1' DAY;

With respect to tree queries, sure, there might be practical efficiency
considerations for substituting recursion for a node numbering scheme
like the nested set model. And, for what it does, it's simple and clever.
However, as more is demanded of a query language, pushing it towards
computational completeness, a cornerstone of this is the ability to loop,
as required by Turing completeness. Think now not about tree queries
but about such things as graph algorithms like shortest path or combinatorial
algorithms like all subsets of integers whose sum equals some value (with
no a priori upper bound on the cardinality of such sets). So given the
ultimate necessity to loop, one can loop imperatively (control statements in
T-SQL or SQL PSM) or loop declaratively using recursion (recursive WITH),
the latter being more compatible with the high-level declarative nature of the
relational model and SQL.

--
JAG