SQL for a key plan principle

[dreamquartz]

Hello All,

I am looking for some guidance about the 'key-plan for doors' principle for an HSQLBD.
I am not sure what the correct description is, but it appears to be similar.....
There is a Master who can do all. This Master has a a number of Subs, al uniquely identified.
Every Sub is a 'Sub-Master' to those who are below them.
Every Sub can have multiple Sub-Subs, and every Sub-Sub is also uniquely identified.
This principle can go on and on and on.
It almost looks like a Christmas Tree.

I need to be able to always be able to identify:
1. the (Sub-)Master, even if that is many levels up...
2. and via the identified (Sub-)Master identify any Sub below that Level.
My question is: Do I have to add a new identifier for every layer, or is there a simpler method?

Example:
MasterID: A, R, Q, S
SubIDs: B, E, D, K, H, M
Therefore they can be identified as AB, AE .... AM
Sub-SubID: 3, 5, 9, 1
Therefore as Subs to SubID: AE, they can be identified as AE3, AE5, ..AE1

All the Keys are the table tKeyTable, with a Primary Key: KeyTableID

Any suggestions are mutch appreciated

Dream

[Villeroy]

I'm not sure if this path is viable.
A single self-referential table:

Code: Select all

CREATE TABLE KEYS(
N VARCHAR(12) NOT NULL,
ID INT IDENTITY,
PKEY INT,
FOREIGN KEY (PKEY) REFERENCES KEYS(ID),
CONSTRAINT "NOT_SELF" CHECK (PKEY != ID),
CONSTRAINT "UNIQ_N" UNIQUE(N)
)

The parent key PKEY is nullable. Keys without any parent are top master keys.
The form starts on the left side with one master key. Below there is a form to add a new key with any parent. The list box is unfiltered, but the constraint "NOT_SELF" takes care that no self-referencing keys can be stored.
Then you have sub-forms for level 2, 3, 4 and 5, each with a form to add new entries with the same parent. Any new key on level x will inherit the currently selected parent on level x-1.

[dreamquartz]

Thanks Villeroy,
Will look into it.

[Villeroy]

I made a mistake on form L5. "Add data only" is True, should be False.
The following query creates a hierarchical overview:

Code: Select all

SELECT K1.N AS L1,K2.N AS L2,K3.N AS L3,K4.N AS L4,K5.N AS L5 
FROM KEYS K1
 LEFT JOIN KEYS K2 ON K2.PKEY = K1.ID
 LEFT JOIN KEYS K3 ON K3.PKEY = K2.ID
 LEFT JOIN KEYS K4 ON K4.PKEY = K3.ID
 lEFT JOIN KEYS K5 ON K5.PKEY = K4.ID
WHERE K1.PKEY IS NULL

[dreamquartz]

It appears that this is exactly what I am looking for.
The reason for my question is that if there are 2 keys, not necessarily in the same Cluster, in this case the "Boss Key", is how to determine what the common key is. It could be on level up or all the way to the "Boss Key".

Will work on it further.

Dream

[dreamquartz]

@Villeroy,

Questions:
The following part is not a 100% clear:

Code: Select all

CONSTRAINT "NOT_SELF" CHECK (PKEY != ID)

I cannot find any info about "NOT_SELF" in any literature, and what does

Code: Select all

(PKEY != ID)

exactly mean?

The principle works for a single Master Key, but in our situation we have Multiple Master Keys that do have sometimes overlap for the same 'Sub Key'.
It might also be so that we have a key a number of levels down, and need to determine if a potential other Master Key can be used.
This might not have to be @ the top level, but maybe @ a certain sub-level.

Example
Master Key A, L5 sub Key.
There are Master Key B, Master Key C, even more.
Master Key K, L3 sub Key might be already a 'Master Key' for Master Key A, L5 sub Key.

The linking, and therefore grouping of keys should be possible for any combination.
The identification as a Master Key should be possible for any other (sub) key.

I did not know that these were the requirements at first, so I hope for some guidance.

Dream

[robleyd]

I cannot find any info about "NOT_SELF" in any literature

I am by no means an expert on HSQL, but a quick glance at the documentation suggests to me that it is the user defined name of the constraint. As is apparent from Villeroy's note but the constraint "NOT_SELF" takes care that no self-referencing keys can be stored.

what does (PKEY != ID) exactly mean?

Checking that no self-referencing keys can be stored as mentioned above. In other words, the fields PKEY and ID may not be the same in any record.