I was hoping someone would look at this situation first, and someone did.
However, the answer about needing to define a PK before you can use it as a
FK being a limitation of SQL may have been a bit misleading (sorry, Jarek).
It is more of a building block of relational theory rather than a
limitation of SQL.
If I understand Anthony's problem with using level and directory name as
the parts of the PK, it's that you cannot create the following situation:
(root)
+users
+bob
+public_html
+larry
+public_html
This is because public_html would be at level 3 (or 4 if you start counting
at 1, but I don't so it's 3) and you cannot create two instances of (3,
public_html) in a unique index.
So what, then, is the solution to the problem?
If it were my project, I'd just model the structure as a classic tree.
It's not magic, it's not a secret, and it's time-tested. Yes, it does
involve creating a unique primary key, though.
CREATE TABLE directory (
id int,
name varchar(32),
parent int,
profile_id varchar(16),
group_id varchar(16),
permission char(9),
create_dt timestamp,
modify_dt timestamp,
PRIMARY KEY(id)
);
CREATE UNIQUE INDEX ix_directory_parent ON directory (id, parent);
And, of course, the foreign key constraints and customary column constraints.
That's it. No levels. You don't need them. You could store them if you
really really want to (why?), but only as advisory data, not functional
data. No parent directory table needed or desired. This example pretty
well accurately models directory tree structures.
You could do something like this (but you would lose some functionality):
CREATE TABLE directory (
name varchar(32),
parent varchar(32),
...
PRIMARY KEY(name, parent)
);
What you lose is the ability for bob and larry to have identical subtree
structures. You can have /users/bob/data/base, but then larry could only
have /users/bob/data (he could not have the base subdirectory because bob's
key (data, base) would conflict with larry's).
To get around this, you still have to calculate a unique key somehow. It
could be meaningful, I suppose. Something like this:
CREATE TABLE directory (
fullpath varchar(255),
name varchar(32),
parent varchar(255),
...
PRIMARY KEY(fullpath)
);
Fullpath would be calculated to be the fullpath of the parent plus the name
of the directory.
This imposes a limitation on the length of fullpath (unless your RDBMS can
store unlimited length varchars). But it may offer some added search
performance in exchange for the additional disk space necessary to house
this table. I think, though, that join performance would suffer. So what
do you do more of: searches or joins?
It was pointed out several weeks ago that the advantage to using a 2nd
table would be so that a single directory can have two parents. There are
advantages and disadvantages to modelling this by separating the
relationship into two tables or leaving it as a single table with a column
that indicates the directory's "type." A single table with a type column
doesn't require joining three tables all the time (directory,
parent_directory, and directory again).
I hope all of this helps at least somewhat.
Doug
At 11:37 AM 12/11/00 -0800, Anthony Martin wrote:
>By the way, this is on an Interbase database, but the logic should work for
>any properly written referential database...
>
>I'm having trouble figuring out this referential logic. I'm writing a
>rudimentary directory structure for dividing HTML sections from one another.
>Right now, my tables have no referential integrity, except for ones
>pertaining outside the directory structure (those being profiles and
>groups). My problem is that I cannot find a way to declare primary keys and
>hence foreign keys yet maintain the functionality I'm looking for.
>
>Here are my tables:
>
>CREATE TABLE "DIRECTORY" (
> "DEPTH" INTEGER NOT NULL,
> "NAME" VARCHAR(32) NOT NULL,
> "PROFILE_ID" VARCHAR(16),
> "GROUP_ID" VARCHAR(16),
> "PERMISSION" CHAR(9),
> "CREATE_DT" TIMESTAMP,
> "MODIFY_DT" TIMESTAMP,
> CONSTRAINT "DIRECTORY_PROFILE_FK" FOREIGN KEY ( "PROFILE_ID" ) REFERENCES
>"PROFILE" ( "ID" ),
> CONSTRAINT "DIRECTORY_GROUP_FK" FOREIGN KEY ( "GROUP_ID" ) REFERENCES
>"GROUP" ( "ID" )
>) ;
>
>GRANT SELECT, INSERT, UPDATE, DELETE ON "DIRECTORY" TO "PHP" ;
>
>
>---
>CREATE TABLE "PARENT_DIRECTORY" (
> "PARENT_DIR_DEPTH" INTEGER NOT NULL,
> "PARENT_DIR_NAME" VARCHAR(32) NOT NULL,
> "CHILD_DIR_DEPTH" INTEGER NOT NULL,
> "CHILD_DIR_NAME" VARCHAR(32) NOT NULL
>) ;
>
>GRANT SELECT, INSERT, UPDATE, DELETE ON "PARENT_DIRECTORY" TO "PHP" ;
>
>
>---
>The records that get inserted would be as follows:
>
>INSERT INTO "DIRECTORY" (
> "DEPTH", "NAME", "PROFILE_ID", "GROUP_ID", "CREATE_DT", "MODIFY_DT"
>) VALUES (
> 0, '%ROOT%', 'SYSTEM', 'SYSTEM', 'NOW', 'NOW'
>) ;
>
>INSERT INTO "DIRECTORY" (
> "DEPTH", "NAME", "PROFILE_ID", "GROUP_ID", "CREATE_DT", "MODIFY_DT"
>) VALUES (
> 1, 'ANTHONY', 'SYSTEM', 'SYSTEM', 'NOW', 'NOW'
>) ;
>
>INSERT INTO "PARENT_DIRECTORY" (
> "PARENT_DIR_DEPTH", "PARENT_DIR_NAME", "CHILD_DIR_DEPTH", "CHILD_DIR_NAME"
>) VALUES (
> 0, '%ROOT%', 1, 'ANTHONY'
>) ;
>
>---
>The answer is staring me in the face: use artificial primary keys. But I've
>gotten away with avoiding them for the whole project. As you can see, I
>avoid artificial primary keys by using the depth and name combination. But
>in order to have multiple directory entries in the same depth, I have to
>allow non-unique entries. To allow non-unique entries, I can't have primary
>keys. If I can't have primary keys, I can't have foreign keys pointing to
>them.
>
>Any thoughts?
>
>
>Anthony
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
