Date/Time

Jan, 2012

Legend

T: text
R: required
PK,FK: Primary key, Foreign key
UFK: used as a foreign key somewhere else
A: auto sequence/identity
N: number
SN: number generated through an explicit sequence
I: index
D: date/time

Users

user_id         T(50),R,PK
first_name      T(50),R, 
middle_name     T(50),
last_name       T(50),R
password        T(50)
email           T(255)
active (Y/N)    T(1:Y),R
master_page_template_item_id N

Folders

folder_id               A,
folder_name             T(50),R,I
parent_folder_id        N,PK
public (Y/N)            T(1:N)
secure (Y/N)            T(1:N)
owner_user_id           T(50),FK,R
folder_short_description T(Long)
folder_long_description  T(long)
folder_child_segment     T(long)
folder_last_updated_on   D,R
folder_last_updated_by   D,R

Reports

report_id           SN(report_sequence),PK,
report_short_name   M
report_long_name    M
report_description  M
public (yes/no)     B(:YES)
url                 M
update_url          M
field_values        M
where_clause        M
report_type_id      N,FK
report_content_id   N,FK(sql_statements.statement_id)
owner_user_id       T(50),FK
created_by          T(50),FK
created_on          D
last_updated_by     T(50),FK
last_updated_on     D

A content item is stored here. report_id is the id that is used to display an i tem in akc. The body of the item is stored in the sql_statements. The id of that table is kept in report_content_id. The value for report_id is obtained from a sequence stored in aspire_sequences table.The name of this sequence is "report_sequence". An item's body is first stored in sql_statements and then the sql_statements.statement_id is used to insert the row in the reports table.

sql-statements

id              A,
statement_id    SN(sql_report_sequence),UFK, PK
statement       M
database_name   T(50)

the statement_id is a manual sequence. name of the sequence is sql_report_sequence and is kept in aspire_sequences table. The "id" field is currently not used, but could have been. However the "id" field is auto geneated.

filed_items

filed_item_id       A,PK
folder_id           FK
item_id             FK (reports.report_id)
item_type           N

item_type is not used right now. Not sure why it is not in the item table. the item_id is most likely the reports.report_id field.

file_attachments

attachment_id       A,PK
file_id             FK(reports.report_id)
filename            T(255)
filesize            N
owner_user_id       FK(userid)
last_updated_on     D
last_updated_by     FK(userid)

shorturls

surl_id                 A, PK
surl_owner_user_id      FK(user)
surl_map                T(127)
surl_url                T(255)
surl_last_updated_by    FK(user)
surl_last_updated_on    D
surl_uniquemap          T(127)

The surl_uniquemap is created to quickly validate if the combination of userid and a url map is unique. So this column is redundant data but needed for validating uniqueness. This is just a quick work around.

aspire_sequences

seq_name    T(50),PK
seq_value   N

This table is used to come up with sequence numbers for a given sequence name. The current value of the sequence is in seq_value. This value gets incremented everytime a new sequence number is needed for the given sequence. In retrospect i think auto numbers may work better.

report-types

Deprecate this as well. I haven't used it in a while. It shouldn't influence the current functionality.

report_type_id an
report_type_name
report_type_table_name
report_type_column_name

Deprecated Tables

applications
application_notes
topics
notes
report_types
development_type

Appendix-A: list of identity columns

folders.folder_id                       
sql_statements.id                       
filed_items.filed_item_id               
file_attachments.attachment_id          
shorturls.surl_id