Rather than having several lookup or ancilliary tables to store valid values for a given data element, we are utilizing a table of tables scheme. This table contains a short name for a lookup table and a brief description. The related detail data for the lookup values used by each of those tables is stored in the master codes (MstrCd) table.

use StudentCentered
go

if exists (select 1
from sysindexes
where id = object_id('dbo.TblOfTbl')
and name = 'EndDt_IDX'
and indid > 0
and indid < 255)
drop index dbo.TblOfTbl.EndDt_IDX
go

if exists (select 1
from sysindexes
where id = object_id('dbo.TblOfTbl')
and name = 'Nm_IDX'
and indid > 0
and indid < 255)
drop index dbo.TblOfTbl.Nm_IDX
go

if exists (select 1
from sysobjects
where id = object_id('dbo.TblOfTbl')
and type = 'U')
drop table dbo.TblOfTbl
go

/*==============================================================*/
/* Table: TblOfTbl */
/*==============================================================*/
create table dbo.TblOfTbl (
ID int not null,
Nm varchar(100) not null,
Descr varchar(255) not null,
CrtrID varchar(18) not null default suser_sname(),
CrtDt datetime not null default getdate(),
UpdtID varchar(18) not null default suser_sname(),
UpdtDt datetime not null default getdate(),
EndDt datetime null,
constraint TblOfTbl_PK primary key nonclustered (ID)
on SmallIDX
)
on SmallTBL
go

/*==============================================================*/
/* Index: Nm_IDX */
/*==============================================================*/
create index Nm_IDX on dbo.TblOfTbl (
Nm
)
on SmallIDX
go

/*==============================================================*/
/* Index: EndDt_IDX */
/*==============================================================*/
create index EndDt_IDX on dbo.TblOfTbl (
EndDt
)
on SmallIDX
go

Unique ID assigned and maintained by the database for the TblOfTbl table.

Name of the virtual table.

Description for the virtual table.

The user identification of the person or process that created the record.

The date the source row of data was created.

The user identification of the person or process that last updated the record.

The date of the last update.

The date that this row of data ceases to be effective.