A. The common table structure The tables presented here are a streamlining (i.e. fields and some tables are not listed) of a more structured definition that covered the presence of multiple customers, applications, and potentially multiple tenants (as for a time I had a “hosting reseller” agreement).
Furthermore, tables were actually “unbundled”, with some fields replicated in few tables, on the presentation side of the database, to accelerate retrieval.
Most of the “update” side of applications using this structure was actually stored offline, i.e. there was a “release and publish” sequence associated with activities.
The design (this is the early 2000s version) enabled to transfer between databases online and offline or replicate without reference a specific infrastructure, but the “security key” was designed to be specific to each site.
B. Common table fields Each streamlined table has the following fields:
- APP: application
- 00: unique numeric key
- 01: datatype
- S1: status1
- S2: status2
- S3: creation user key
- S4: creation timestamp
- S5: update user key
- S6: update timestamp
- CK: checksum, ie md5 of all the fields
C. TABX00, the “traffic cop” table This table is a list of “data types” (e.g. magazine, issue, section, paragraph); after the column 01 derived from the standard table, it contains just a column, before the CU/CD/UU/UD/ST/CK standard columns:
02: table where the datatype is defined
D. TABX01…TABX04 associations These tables have all the same structure- the only difference being that e.g. datatypes defined in table TABX01 do not require an assocation with other datatypes to qualify their use, while TABX02 requires one association (datatype+key), TABX03 two associations, TABX04 three associations.
E. TABX05…TABX09 and TABXAA processing These tables contain descriptions, XML definitions, etc. for datatype that need it; these table also add the possibility to store extensive text, and processes to be executed per association with a datatype (e.g. specific post- or pre-processing needed for certain datatypes, but in a form that does not depend from a specific database).
F. SAMPLE SQLITE and SQL definitions I will not share the whole database, but a version streamlined as listed above, containing all the fields definitions, but without the typical primary key, unique, etc, and removing all the definitions that aren’t supported by SQLite
If you download the files, please not that clauses such as “AUTO_INCREMENT”, “unsigned”, “primary key”, etc. are not included,
Anyway, with the information above, if interested you can recycle and modify according to your needs.
The book explaining few (past and potential) applications should be released in July 2019, as part of the “Connecting the dots” series.
Follow the menu options on top.
You can contact me on Linkedin.com