The DataSetNumber
The DataSetNumber is a unique number that identifies each dataset, and it appears on every row within that dataset. In simpler terms, let's say tbl_person has a DataSetNumber of 343 for every record, and no other dataset recognized by the system shares the same 343.
While it may look like repetitive data or go against typical data organization rules, let's approach it from a different perspective.
Imagine combining two tables using just their Ids. This might lead to issues, like having the same Id in both tbl_person and tbl_group. Introducing the DataSetNumber to our combined data helps avoid these clashes. Therefore, this value relies on the primary key to ensure unique identification at the system level.
Typically, we remove duplicate data and store it in a lookup table with a foreign key constraint for joining. However, in this case, it's not possible because this value is a superset of the object it belongs to. A lookup table join would need to be based on a literal string defining the dataset's name. At this point, it's more like a selection (SELECT Id, @Value FROM tbl_person) than a traditional join.
Surprisingly, the DataSetNumber is a logical outcome of a record's existence.
Pretty crazy, right?
Having this column in every dataset now allows us to use a single Relationship table (with its own RCFT) to join any table with any other table.
To prepare for this, we need a simple function that establishes our default constraints for the DataSetNumber and can update all records in the table not meeting this constraint.
Code
This function is to ensure the DEFAULT constraint is properly set and all records in the table match the DataSetNumber definition.
It is written in Postgres to demonstrate how to navigate systems that do not allow you to modify the table in the cursor.
I converted this function to query tbl_dataset as json, bypassing the active table error.
1
2
3DECLARE
4 dsCursor NO SCROLL CURSOR FOR
5 select sysdatasetid, strname
6 from tbl_dataset
7 WHERE strname!='tbl_dataset';
8 ds RECORD;
9 dataset_id bigint;
10BEGIN
11 dataset_id := (select sysDataSetId from tbl_dataset where strname = 'tbl_dataset');
12 EXECUTE format('ALTER TABLE %I ALTER COLUMN intDataSetNumber SET DEFAULT %s','tbl_dataset',dataset_id);
13 OPEN dsCursor;
14 LOOP
15 FETCH NEXT FROM dsCursor INTO ds;
16 EXIT WHEN NOT FOUND;
17 EXECUTE format('UPDATE %I SET intDataSetNumber = $1 WHERE intDataSetNumber != $1',ds.strName)
18 USING ds.sysdatasetid;
19 EXECUTE format('ALTER TABLE %I ALTER COLUMN intDataSetNumber SET DEFAULT %s',ds.strName,ds.sysdatasetid);
20 END LOOP;
21 CLOSE dsCursor;
22END;
23
24